一个列转行SQL示例（wm_concat函数和replace函数合用），

一个列转行SQL示例（wm_concat函数和replace函数合用），

 

准备测试数据：

create table test01(

groupid      number,

a            number,

b            number,

c            number

);

insert into test01 values(1,2,3,4);

insert into test01 values(1,2,6,4);

insert into test01 values(1,7,3,9);

insert into test01 values(2,2,0,4);

insert into test01 values(2,8,3,4);

insert into test01 values(2,5,3,4);

insert into test01 values(3,8,3,4);

insert into test01 values(3,9,3,4);

insert into test01 values(3,4,3,4);

select * from test01;

SQL展示结果如下：

SQL> select groupid,

  2         replace(wm_concat(a), ',', '') as A,

  3         replace(wm_concat(b), ',', '') as B,

  4         replace(wm_concat(c), ',', '') as C

  5    from test01

  6   group by groupid;

   GROUPID A          B          C

---------- ---------- ---------- ----------

         1 272        336        494

         2 258        033        444

         3 849        333        444