关于多行转换成一行(非游标实现)

InFerNaL_LioN 2009-01-21 12:21:07

请问如下转换如何只用查询就可以实现？
COL1 COL2 COL3
A 1 who
A 2 are
A 3 you
B 1
B 2 thank
B 3 you

转化后：
COLA COLB
A who, are, you
B thank, you

请注意COL3可能存在空值情况。
感谢!

...全文

138 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

大头_衍 2009-01-22

打赏
举报

留个脚印

watson110 2009-01-21

打赏
举报

转一个牛人dawugui的例子：
/*
标题：合并相同列的数据
作者：爱新觉罗.毓华
时间：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
*/

InFerNaL_LioN 2009-01-21

打赏
举报

用case when 又如何实现？

Raul_Gonzalez 2009-01-21

打赏
举报

mark！～学习！～

hebo2005 2009-01-21

打赏
举报

select col1,WMSYS.WM_CONCAT(col3)

from tb

where col3 is not null

group by col1

hebo2005 2009-01-21

打赏
举报

select col1,WMSYS.WM_CONCAT(col3)

from tb

group by col1

hebo2005 2009-01-21

打赏
举报

10G用WMSYS.WM_CONCAT这个函数就可以实现
9I用SYS_CONNECT_BY_PATH
看此贴
http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048

zerler 2009-01-21

打赏
举报

不会牛X的办法，笨点的用case when就可以。

fosjos 2009-01-21

打赏
举报

搜一下就可以了，10g前用connect by ，10g后可以用专门的函数
空值用is not null过滤记录就可以了

ACMAIN_CHM 2009-01-21

打赏
举报

select a1.COL1,nvl(a1.COL3,'') || nvl(a2.COL3,'')  || nvl(a3.COL3,'') 

from 

(select COL1,COL3 from tb where COL2=1) a1,

(select COL1,COL3 from tb where COL2=2) a2,

(select COL1,COL3 from tb where COL2=3) a3

where a1.COL1=a2.COL1 and a1.COL1=a3.COL1