合并相同列的数据（引用）

/*
标题：合并相同列的数据
作者：爱新觉罗.毓华
时间：2008-05-05
地点：广东深圳
*/

/*情况说明
NAME USERID
张三 KB001
张三 KB003
李四 KB001
李四 KB002
李四 KB003

用SQL实现表示如下:
NAME USERID
张三 KB001,KB003
李四 KB001,KB002,KB003
*/

create table tb(NAME varchar2(10) , USERID varchar2(10))
insert into tb values('张三' , 'KB001');
insert into tb values('张三' , 'KB003');
insert into tb values('李四' , 'KB001');
insert into tb values('李四' , 'KB002');
insert into tb values('李四' , 'KB003');

--1
select name,rtrim(
  max(decode(USERID , 'KB001' , USERID || ',' , '')) ||
  max(decode(USERID , 'KB002' , USERID || ',' , '')) ||
  max(decode(USERID , 'KB003' , USERID || ',' , '')),',') userid
from tb
group by name

--2
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid , ',')) , ',') userid
FROM (SELECT NAME , userid , row_number() over(PARTITION BY NAME ORDER BY userid) rn , rownum prn FROM tb)
START WITH rn = 1
CONNECT BY prn - 1 = PRIOR prn AND NAME = PRIOR NAME
GROUP BY NAME
ORDER BY NAME;

drop table tb

/*
NAME       USERID                          
---------- ---------------------------------
李四       KB001,KB002,KB003               
张三       KB001,KB003                     
2 rows selected
*/

 

 

create table t2(col1 varchar2(10),col2 varchar2(10));
insert into t2 values('001','vl1');
insert into t2 values('001','vl2');
insert into t2 values('001','vl3');
insert into t2 values('002','vl1');
insert into t2 values('002','vl2');

SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2
FROM (SELECT COL1,
               COL2,
               MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
               (ROW_NUMBER() OVER(ORDER BY COL1, COL2)) +
               (DENSE_RANK() OVER(ORDER BY COL1)) NUMID
          FROM T2)
START WITH COL2 = COL2_MIN
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY COL1

/*
COL1       COL2                                   
---------- ----------------------------------------
001        vl1,vl2,vl3                            
002        vl1,vl2                                
2 rows selected
*/