sql行转列

在实际的业务开发过程当中，会碰到oracle表数据默写列数据转换成一行如用逗号分隔，常常会碰到。

有张表 cardInof

type             tel

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

11               aa

11               bb

11               cc

22               ee

22               sd

 

用一条sql语句转换成如下格式

 

type                tel

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

11                   aa,bb,cc

22                   ee,sd

 

这里是数据

 

create table CARDINFO
(
  type VARCHAR2(5),
  TEL  VARCHAR2(5)
)
;

执行数据的插入

insert into CARDINFO (type, TEL)
values ('11', 'aa');
insert into CARDINFO (type, TEL)
values ('11', 'bb');
insert into CARDINFO (type, TEL)
values ('11', 'cc');
insert into CARDINFO (type, TEL)
values ('22', 'dd');
insert into CARDINFO (type, TEL)
values ('22', 'sd');
commit;

 

 

SQL>
SQL> SELECT type, MAX(Substr(Sys_Connect_By_Path(TEL, ','), 2)) ret
  2    FROM (SELECT type, TEL, row_number() over(PARTITION BY type ORDER BY TEL) rn FROM CARDINFO)
  3   START WITH rn = 1
  4  CONNECT BY PRIOR rn = rn - 1
  5         AND PRIOR type= type
  6   GROUP BY type
  7   ORDER BY type
  8  /
 
type SCBP
----- --------------------------------------------------------------------------------
11    aa,bb,cc
22    dd,sd

 

还可以使用oracle那边的函数实现如上的功能

利用函数包来完成wmsys.wm_concat
select type,wmsys.wm_concat(tel) from t group by type;