假如:
id sourceid tarName sourceName
1 1001 a 小王
1 1002 a 小黄
1 1003 a 小黑
1 1004 a 小陈想要的结果:
id sourceid tarName sourceName
1 1001,1002,1003,1004 a 小王,小黄,小黑,小陈
id sourceid tarName sourceName
1 1001 a 小王
1 1002 a 小黄
1 1003 a 小黑
1 1004 a 小陈想要的结果:
id sourceid tarName sourceName
1 1001,1002,1003,1004 a 小王,小黄,小黑,小陈
from tab
group by id,tarname
(SELECT WM_CONCAT(SOURCEID) FROM TABLE_NAME T WHERE T.ID = TN.ID) SOURCEID,
(SELECT WM_CONCAT(TARNAME) FROM TABLE_NAME T WHERE T.ID = TN.ID) TARNAME,
(SELECT WM_CONCAT(SOURCENAME) FROM TABLE_NAME T WHERE T.ID = TN.ID) SOURCENAME
FROM TABLE_NAME TN
GROUP BY ID
看这个可以不
with t as
(
select 1 b, 1001 x, 'a' y, '小王' z from dual
union all
select 1, 1002, 'a', '小黄' from dual
union all
select 1, 1003, 'a', '小黑' from dual
union all
select 1, 1004, 'a', '小陈' from dual
)select b,max(substr(sys_connect_by_path(x,','),2)) x ,y,max(substr(sys_connect_by_path(z,','),2)) z
from
(
select b,row_number()over(partition by b order by b) rn,x,y,z
from t
)
start with rn=1
connect by rn=prior rn+1 and b=prior b and y=prior y
group by b,y
--result:
1 1001,1002,1003,1004 a 小王,小黄,小黑,小陈
http://blog.csdn.net/roy_88/article/details/6930577
select
id,wmsys.wm_concat(sourceid) as sourceid ,
tarName ,
wmsys.wm_concat(sourceName) AS sourceName
from tab group by id,tarName
/**方法2**/
with Tab
as
(
select 1 as Col1,'a' as Col2 from dual union all
select 1,'b' from dual union all
select 1,'c' from dual union all
select 2,'d' from dual union all
select 2,'e' from dual union all
select 3,'f' from dual
)
select
Col1,substr(max(sys_connect_by_path(Col2,',')),2) Col2
from
(select a.*,row_number()over(partition by Col1 order by Col1) rn from Tab a )
group by Col1 start with rn=1
connect by rn-1=prior rn and Col1=prior Col1
order by Col1;
SQL>
SQL> with t as
2 (
3 select 1 b, 1001 x, 'a' y, '小王' z from dual
4 union all
5 select 1, 1002, 'a', '小黄' from dual
6 union all
7 select 1, 1003, 'a', '小黑' from dual
8 union all
9 select 1, 1004, 'a', '小陈' from dual
10 )
11 select b,max(substr(sys_connect_by_path(x,','),2)) x ,y,max(substr(sys_connect_by_path(z,','),2)) z
12 from
13 (
14 select b,row_number()over(partition by b order by b) rn,x,y,z
15 from t
16 )
17 start with rn=1
18 connect by rn=prior rn+1 and b=prior b and y=prior y
19 group by b,y
20 ;
B X Y Z
---------- -------------------------------------------------------------------------------- - --------------------------------------------------------------------------------
1 1001,1004,1003,1002 a 小王,小陈,小黑,小黄
SQL>