参考: /* 标题:合并相同列的数据 作者:爱新觉罗.毓华 时间: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 */
如果是10g以上,可参考如下: with tb as( select 'A' grade,'XX'name from dual union all select 'A','XY' from dual union all select 'A','YY' from dual union all select 'B','aa' from dual union all select 'B','bb' from dual) select grade,substr(max(sys_connect_by_path(name,';')),2) name from (select grade,name,row_number() over(partition by grade order by name) rn from tb) start with rn=1 connect by rn= prior rn+1 and connect_by_root(grade)=grade group by grade; -- GRADE NAME ----- -------------------------------------------------------------------------------- A XX;XY;YY B aa;bb -- 10g的实现: with t as( select 'A' grade,'XX'name from dual union all select 'A','XY' from dual union all select 'A','YY' from dual union all select 'B','aa' from dual union all select 'B','bb' from dual) select grade,wmsys.wm_concat(name) name from t group by grade; -- GRADE NAME ----- -------------------------------------------------------------------------------- A XX,XY,YY B aa,bb
/*
标题:合并相同列的数据
作者:爱新觉罗.毓华
时间: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
*/
with tb as(
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual)
select grade,substr(max(sys_connect_by_path(name,';')),2) name
from (select grade,name,row_number() over(partition by grade order by name) rn from tb)
start with rn=1
connect by rn= prior rn+1 and
connect_by_root(grade)=grade
group by grade;
--
GRADE NAME
----- --------------------------------------------------------------------------------
A XX;XY;YY
B aa;bb
--
10g的实现:
with t as(
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual)
select grade,wmsys.wm_concat(name) name
from t
group by grade;
--
GRADE NAME
----- --------------------------------------------------------------------------------
A XX,XY,YY
B aa,bb