求sql：按主键group by后，名称字段用逗号连接起来

求sql：按主键group by后，名称字段用逗号连接起来
如

Create   Table   #TEST       
(ID Varchar(10),         Name   Nvarchar(10))       
Insert   #TEST   Values('01',       N'小张')       
Insert   #TEST   Values('01',       N'小王')       
Insert   #TEST   Values('02',       N'小王')       
Insert   #TEST   Values('02',       N'小陈')
Insert   #TEST   Values('02',       N'张三')
Insert   #TEST   Values('03',       N'李四')
Insert   #TEST   Values('03',       N'王武')  

希望得到的结果：

id    name
01    小张，小王
02    小王，小陈，张三
03    李四，王武

10 个解决方案

#1

select id,
name=stuff((select ','+name from #test where id=t.id for xml path('')),1,1,'')
from #test t
group by id

#2

Create   Table   #TEST       
(ID Varchar(10),         Name   Nvarchar(10))       
Insert   #TEST   Values('01',       N'小张')       
Insert   #TEST   Values('01',       N'小王')       
Insert   #TEST   Values('02',       N'小王')       
Insert   #TEST   Values('02',       N'小陈')
Insert   #TEST   Values('02',       N'张三')
Insert   #TEST   Values('03',       N'李四')
Insert   #TEST   Values('03',       N'王武')  


select id,(select name+',' from #TEST where id=t.id for xml path(''))
 from #TEST t group by id
/*
id         
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01         小张,小王,
02         小王,小陈,张三,
03         李四,王武,

(3 行受影响)

*/

#3

create function test 
returns varchar(1000)
as
begin
declare @ename varchar(1000)
set @ename = ''
select @ename = @ename+','+rtrim(isnull(name,''))  from #test where
return stuff(@ename,1,1,'')
end

#4

Create   Table   #TEST       
(ID Varchar(10),         Name   Nvarchar(10))       
Insert   #TEST   Values('01',       N'小张')       
Insert   #TEST   Values('01',       N'小王')       
Insert   #TEST   Values('02',       N'小王')       
Insert   #TEST   Values('02',       N'小陈')
Insert   #TEST   Values('02',       N'张三')
Insert   #TEST   Values('03',       N'李四')
Insert   #TEST   Values('03',       N'王武')  


select id,stuff((select ','+name from #TEST where id=t.id for xml path('')),1,1,'')
 from #TEST t group by id
/*
id         
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01         小张,小王
02         小王,小陈,张三
03         李四,王武

(3 行受影响)



*/

#5

补充我的事2000写法，2005可以像1楼2楼那样

#6

select id,stuff((select ','+name from #TEST where id=t.id for xml path('')),1,1,'')
 from #TEST t group by id

#7

----SQL2000以上版本:
--> 生成测试数据表: test
IF OBJECT_ID('TEST') IS NOT NULL

    DROP TABLE TEST

GO

CREATE TABLE TEST (ID Varchar(10), Name   Nvarchar(10))

INSERT INTO TEST

SELECT '01', N'小张' UNION ALL

SELECT '01', N'小王' UNION ALL

SELECT '02', N'小王' UNION ALL

SELECT '02', N'小陈' UNION ALL

SELECT '02', N'张三'union all
SELECT '03', N'李四' UNION ALL
SELECT '03',N'王武'

 

--SELECT * FROM test
 

-->SQL查询如下:

--SQL2000以上版本:

IF OBJECT_ID('dbo.f_str')>0

    DROP FUNCTION dbo.f_str

GO

CREATE FUNCTION dbo.f_str

(

    @id INT

)

RETURNS VARCHAR(1000)

AS

BEGIN

    DECLARE @r VARCHAR(1000)

    SELECT @r = ISNULL(@r+',', '') + name

    FROM test

    WHERE id = @id

    RETURN @r

END

GO
--查询
SELECT DISTINCT id,dbo.f_str(id) name from test

--结果
/*
id             name
------     -------------
01	    小张,小王
02	    小王,小陈,张三
03	    李四,王武
*/

#8

-为什么把#test该为test 自定义函数中不支持临时表#test

#9

--上边代码不够紧凑又修改了一下如下
--> 生成测试数据表: [tb]
IF OBJECT_ID('TEST') IS NOT NULL
    DROP TABLE TEST
GO
CREATE TABLE TEST (ID Varchar(10), Name   Nvarchar(10))
INSERT INTO TEST
SELECT '01', N'小张' UNION ALL
SELECT '01', N'小王' UNION ALL
SELECT '02', N'小王' UNION ALL
SELECT '02', N'小陈' UNION ALL
SELECT '02', N'张三'union all
SELECT '03', N'李四' UNION ALL
SELECT '03',N'王武'

--SELECT * FROM [tb]

-->SQL查询如下:
--SQL2000以上版本:
IF OBJECT_ID('dbo.f_str')>0
    DROP FUNCTION dbo.f_str
GO
CREATE FUNCTION dbo.f_str
(
    @id INT
)
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @r VARCHAR(1000)
    SELECT @r = ISNULL(@r+',', '') + name
    FROM test
    WHERE id = @id
    RETURN @r
END
GO
--查询
SELECT DISTINCT id,dbo.f_str(id) name from test
--结果
/*
id             name
------     -------------
01        小张,小王
02        小王,小陈,张三
03        李四,王武
*/

#10

Create   Table   #TEST       
(ID Varchar(10),         Name   Nvarchar(10))       
Insert   #TEST   Values('01',       N'小张')       
Insert   #TEST   Values('01',       N'小王')       
Insert   #TEST   Values('02',       N'小王')       
Insert   #TEST   Values('02',       N'小陈')
Insert   #TEST   Values('02',       N'张三')
Insert   #TEST   Values('03',       N'李四')
Insert   #TEST   Values('03',       N'王武')  
;with cte as
(
	select id,
		   MAX(case when row=1 then Name+',' else '' end)+
		   MAX(case when row=2 then Name+',' else '' end)+
		   MAX(case when row=3 then Name+',' else '' end)+
		   MAX(case when row=4 then Name+',' else '' end)as jg
	from (select ROW_NUMBER()over(partition by id order by (select 1))as row,
				 id,name
		  from #TEST)as t
	group by id
)
select id,LEFT(jg,LEN(jg)-1)as jb
from cte;
/**
id         jb
---------- --------------------------------------------
01         小张,小王
02         小王,小陈,张三
03         李四,王武

(3 行受影响)
**/