oracle中将列转化为行

oracle 10g以后 wm_concat函数的运用

准备测试数据

 create table test(id number,name varchar2(20));

insert into test values(1,'a');

insert into test values(1,'b');

insert into test values(1,'c');

insert into test values(2,'d');

insert into test values(2,'e');

效果1 : 行转列
SQL> select wm_concat(name) from test;
WM_CONCAT(NAME)
a,b,c,d,e

效果2: 把结果里的逗号替换成"|"
SQL> select replace(wm_concat(name),',','|') from test;
REPLACE(WM_CONCAT(NAME),',','|')
a|b|c|d|e

效果3:按ID分组合并name
SQL> select id,wm_concat(name) name from test group by id;
ID NAME
1 a,b,c