求行合并的SQL

[tomcat]

有一个表结构如下
col1 col2 col3
--------------
a b c
a b d
e f g
e f h

想求一条sql
搜索之后的结果为
col1 col2 col3
--------------
a b c,d
e f g,h

十分感谢~

[cosio]

with t as
(
select 'a' a, 'b' b, 'c' c from dual 
union all
select 'a' a, 'b' b, 'd' c from dual 
union all
select 'e' a, 'f' b, 'g' c from dual 
union all
select 'e' a, 'f' b, 'h' c from dual 
)
select a,b,max(substr(sys_connect_by_path(c,','),2))
from 
      ( select a,b,c,row_number() over(partition by a,b order by a) rn from t)
 start with rn=1  connect by rn-1=prior rn and a=prior a
 group by a,b
 order by a,b


--result:

a	b	c,d
e	f	g,h

[Kobayashi]

顶楼上的。。。

[benson_xi]

正解
[Quote=引用 1 楼 cosio 的回复:]
SQL code

with t as
(
select 'a' a, 'b' b, 'c' c from dual
union all
select 'a' a, 'b' b, 'd' c from dual
union all
select 'e' a, 'f' b, 'g' c from dual
union all
select 'e' a, 'f' b, 'h'……
[/Quote]

[BenChiM888]

with t as
(
select 'a' a, 'b' b, 'c' c from dual 
union all
select 'a' a, 'b' b, 'd' c from dual 
union all
select 'e' a, 'f' b, 'g' c from dual 
union all
select 'e' a, 'f' b, 'h' c from dual 
)
select a,b,wm_concat(c)
  from t
 group by a,b;

[tomcat]

感谢各位的帮助，十分感谢，已经结贴

[cutebear2008]

厉害！
[Quote=引用 1 楼 cosio 的回复:]

SQL code
with t as
(
select 'a' a, 'b' b, 'c' c from dual
union all
select 'a' a, 'b' b, 'd' c from dual
union all
select 'e' a, 'f' b, 'g' c from dual
union all
select 'e' a, 'f' b, 'h' c from d……
[/Quote]