求字符串合并问题，谢谢

[sujian2004515]

例表：
no name jiner you
01 ss 10 麦子
02 dd 11 萝卜
03 cc 9 大豆
01 ss 10 白菜
求表

no name jiner you
01 ss 10 麦子，白菜
02 dd 11 萝卜
03 cc 9 大豆

看看大家咋么实现，另外在oracle和SQL中是否一样实现，谢谢

[suiziguo]

10g及以上：
select no,name,jiner,wm_concat(you) you from table;

9i及以前的需要用sys_connect_by_path。

[iqlife]

select no,name,jiner,wm_concat(you)
from tab
group by no,name,jiner

[suiziguo]

忘了group by，
select no,name,jiner,wm_concat(you) you from table
group by no,name,jiner;

[Phoenix_99]

10g用wm_concat

[tangren]

-9i写一个
WITH tab AS(
  select '01' no,'ss' name, 10 jiner,'麦子' you from dual union all
  select '02' no,'dd' name, 11 jiner,'萝卜' you from dual union all
  select '03' no,'cc' name, 9  jiner,'大豆' you from dual union all
  select '01' no,'ss' name, 10 jiner,'白菜' you from dual)
SELECT a.no, a.name, a.jiner, ltrim(MAX(sys_connect_by_path(you, ',')), ',') you
  FROM (SELECT row_number() over(PARTITION BY a.no, a.name, a.jiner 
         ORDER BY a.no, a.name, a.jiner) rn,a.*
          FROM tab a) a
 START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND
           a.no = PRIOR a.no AND
           a.name = PRIOR a.name AND
           a.jiner = PRIOR a.jiner
 GROUP BY a.no, a.name, a.jiner

[Adebayor]

[Quote=引用 5 楼 tangren 的回复:]
SQL code
-9i写一个
WITH tab AS(
select '01' no,'ss' name, 10 jiner,'麦子' you from dual union all
select '02' no,'dd' name, 11 jiner,'萝卜' you from dual union all
select '03' no,'cc' name, 9 jin……
[/Quote]
up

最好在后面加个 order by a.no;

[icecard]

oracle 9i环境下的一种写法：

select no,name,jiner,max(ltrim(sys_connect_by_path(you,',') ,','))you
from (select row_number() over(partition by no order by no)rn,no,name,jiner,you from tab)
start with rn=1
connect by prior no=no
and prior name=name
and prior jiner=jiner
and prior rn+1=rn
group by no,name,jiner
order by no

[没_没_没超疯]

进来学习下