多行合并成一行

例如按名字查看考试成绩情况，拼接在一个列中

数据

create table emp_phone(name varchar2(50),type varchar2(10),score number(9,6),orderid number)
--插入测试数据，1固定电话 2办公电话, 3手机
insert into emp_phone values('张三','数学',95.5,3);
insert into emp_phone values('张三','英语',73.6,2);
insert into emp_phone values('张三','语文',85.3,1);
insert into emp_phone values('李四','数学',52.6,3);
insert into emp_phone values('李四','英语',18,2);
insert into emp_phone values('李四','语文',66,1);

select * from emp_phone

--某一到账下的所有科目情况

1. 方法1 wm_concat ,不可排序

select name,wm_concat(type || ':' || to_char(score,'fm999990.0999999')) AS info 
from emp_phone 
group by name

效果

1. 方法2 LISTAGG ,可排序

SELECT name,LISTAGG(type || ':' || to_char(score,'fm999990.999999'), ',') WITHIN GROUP (ORDER BY orderid) AS info
FROM  emp_phone
GROUP BY name;

效果