oracle实现group_connect
select devicesid,ltrim(max(sys_connect_by_path(channelnum,',')),',') row2col
from (select devicesid,channelnum,
devicesid+(row_number() over(order by devicesid)) node_id,
row_number() over(partition by devicesid order by devicesid) rn
from channels ORDER BY devicesid asc, channelnum asc)
start with rn = 1
connect by node_id-1 = prior node_id
group by devicesid
order by devicesid;


如果含有特殊字符替换掉特殊字符



select b.devicesid,my_concat(b.channelnum) from devices a,channels b where a.deviceid = b.devicesid   
group by b.devicesid,b.channelnum    
ORDER BY b.devicesid asc, b.channelnum asc


  select 9001, 1, a.devicesid, a.rights
    from (
    select devicesid,
                 ltrim(max(sys_connect_by_path(channelnum, ',')), ',') rights
            from (select devicesid,
                         channelnum,
                         REPLACE(devicesid,'@','') + (row_number() over(order by devicesid)) node_id,
                         row_number() over(partition by devicesid order by devicesid) rn
                    from channels
                   ORDER BY devicesid asc, channelnum asc)
           start with rn = 1
          connect by node_id - 1 = prior node_id
           group by devicesid
           order by devicesid
           ) a,
         devices b
   where b.coding like '001%'
     and a.devicesid = b.deviceid

--普通函数，用于分组，连接组内字符串，逗号,不保证顺序，要顺序用下面分析函数，然后取最长的concat_str
WITH t AS
(SELECT MOD(LEVEL,5) ID,LEVEL lv FROM dual
CONNECT BY LEVEL<28)
SELECT ID,wmsys.wm_concat(lv)
FROM t
GROUP BY ID
ORDER BY ID;

--用于分析函数，可以对每行应用分析函数,order by用于分析函数是计算窗口到当前行
WITH t AS
(SELECT MOD(LEVEL,5) ID,LEVEL lv FROM dual
CONNECT BY LEVEL<28)
SELECT ID,lv,wmsys.wm_concat(lv) over(PARTITION BY ID ORDER BY lv) concat_str_order ,
wmsys.wm_concat(lv) over(PARTITION BY ID) concat_str_order
FROM t
ORDER BY ID;

--用connect by实现,可以有顺序，row_number分析函数决定

  WITH t AS
(SELECT MOD(LEVEL,5) ID,LEVEL lv FROM dual
CONNECT BY LEVEL<28)
SELECT ID, ltrim(max(sys_connect_by_path(lv,',')),',') concat_str
FROM (
SELECT ID,lv,row_number() over(PARTITION BY ID ORDER BY lv) rn
FROM t)
CONNECT BY PRIOR rn=rn-1 AND PRIOR ID=ID
GROUP BY ID
ORDER BY ID;

--自定义wmsys.wm_concat的方法
http://www.itpub.net/thread-1332147-1-1.html

--11g listagg函数也可以
WITH t AS
(SELECT MOD(LEVEL,5) ID,LEVEL lv FROM dual
CONNECT BY LEVEL<28)
SELECT ID,listagg(lv,',') within GROUP (ORDER BY ID)
FROM t
GROUP BY ID;