典型的行转列的SQL语句

以ORACLE数据库为例， 将tab表中的tname字段转成以逗号分隔的字符串

 

SQL_1：

 

select substr(max(sys_connect_by_path(tname, ',')), 2)
from (
  select b.tname, b.t1, lead(b.t1, 1) over (order by b.t1) t2
  from (
       select a.tname, row_number() over (order by tname desc) t1 from tab a
  ) b
)
start with t2 is null
connect by prior t1 = t2;

 

SQL_2:

 

select tabtype, wmsys.wm_concat(tname) from tab group by tabtype;

 

select tname, wmsys.wm_concat(tname) over (order by tabtype) from tab;