原始数据为:
doco date qty doc
8000095 2008/11/12 270 8000249
8000095 2008/11/11 900 8000247
8000095 2008/11/14 830 8000258需要得到结果为:
doco date qty doc
8000095 2008/11/12+2008/11/11+2008/11/14 270+900+830 8000249+8000247+8000258要求要一条SQL实现.谢谢!分不够再加.
doco date qty doc
8000095 2008/11/12 270 8000249
8000095 2008/11/11 900 8000247
8000095 2008/11/14 830 8000258需要得到结果为:
doco date qty doc
8000095 2008/11/12+2008/11/11+2008/11/14 270+900+830 8000249+8000247+8000258要求要一条SQL实现.谢谢!分不够再加.
SELECT doco,wmsys.wm_concat(date,'+') over (partition by doco)
from
参考如下:
/*
标题:合并相同列的数据
作者:爱新觉罗.毓华
时间: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
*/
select doco,replace(wmsys.wm_concat(date) over(partition by doco),',','+') val
from tab;楼上错了,,10G及以上版本可以这样
--wm_concat吧
1 select doco,replace(wm_concat(to_char(date1,'yyyy-mm-dd')),',','+') date1,
2 replace(wm_concat(qty),',','+') qty,
3 replace(wm_concat(doc),',','+') doc from tb
4* group by doco
SQL> /DOCO DATE1 QTY DOC
-------------------- ----------------------------------- -------------------- -----------------
8000095 2008-11-12+2008-11-11+2008-11-14 270+900+830 8000249
+8000247+8000258
wm_concat + replace