求一个SQL！A字段相同时候合并B字段

[h99]

表EXIME 字段 ID ,NAME 都是varchar型
当ID一样时候，把NAME放到同一个字段，用逗号隔开。
1 HUANG
1 LI
1 ZHANG
2 LIU
2 MA
3 WU

==
结果
1 HUANG,LI,ZHANG
2 LIU,MA
3 WU

[开启时代]

http://bbs.csdn.net/topics/390299692 参考贴

[發糞塗牆]

select a.ID ,
 stuff((select ','+NAME  from EXIME  b 
        where b.ID =a.ID 
        for xml path('')),1,1,'') 'NAME '
 from EXIME  a
 group by  a.ID

[以学习为目的]

select ID,NAME=stuff((select ','+NAME  from tb where ID =a.ID for xml path('')),1,1,'')
from tb a group by ID

[姐只想升星星]

select 
    a.ID ,
    NAME=stuff((select ','+NAME  from EXIME  b 
where 
    b.ID =a.ID for xml path('')),1,1,'') 'NAME '
from 
    EXIME  a
group by 
    a.ID

[h99]

db2 的 怎么搞？

[發糞塗牆]

干嘛不去DB2那里问啊？浪费大家的时间

[十林]

使用CTE 有点麻烦。不过可行 DB2 和SQL server 2005以上都可以。

 

CREATE TABLE [dbo].[temp](
	[a] [varchar](20) NULL,
	[b] [varchar](20) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[temp] ([a], [b]) VALUES (N'1', N' HUANG')
INSERT [dbo].[temp] ([a], [b]) VALUES (N'1', N'  LI')
INSERT [dbo].[temp] ([a], [b]) VALUES (N'1', N' ZHANG')
INSERT [dbo].[temp] ([a], [b]) VALUES (N'2', N'  LIU')
INSERT [dbo].[temp] ([a], [b]) VALUES (N'2', N'  MA')
INSERT [dbo].[temp] ([a], [b]) VALUES (N'3', N'  WU')
INSERT [dbo].[temp] ([a], [b]) VALUES (N'3', N'MA')
INSERT [dbo].[temp] ([a], [b]) VALUES (N'4', N'ERE')
INSERT [dbo].[temp] ([a], [b]) VALUES (N'4', N'DFSAF')
INSERT [dbo].[temp] ([a], [b]) VALUES (N'4', N'ADFSD')
INSERT [dbo].[temp] ([a], [b]) VALUES (N'4', N'ADFD')


;with  cte(rownum,a,b) as (
 select rownum,a,cast(b as varchar(100))from (select ROW_NUMBER() over (partition by a order by a) as rownum,a,b from dbo.temp) a where a.rownum=1
 union all
 
 select b.rownum,b.a,cast(c.b+b.b as varchar(100)) from (select ROW_NUMBER() over (partition by a order by a) as rownum,a,b from dbo.temp ) b
 inner join cte c
 on c.a=b.a
 and b.rownum=cast(c.rownum as integer)+1

 )
 select * from cte a INNER JOIN 
 (SELECT a,max(rownum) as rown from cte group by a) b  
 ON A.a=b.a
 and a.rownum=b.rown
 order  by a.a