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

Question

求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    李四，王武

Answer 1

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

Answer 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 行受影响)*/

Answer 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

Answer 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 行受影响)*/

Answer 5

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

Answer 6

----SQL2000以上版本:
--> 生成测试数据表: test
IF OBJECT_ID('TEST') IS NOT NULL    DROP TABLE TESTGOCREATE TABLE TEST (ID Varchar(10), Name   Nvarchar(10))INSERT INTO TESTSELECT '01', N'小张' UNION ALLSELECT '01', N'小王' UNION ALLSELECT '02', N'小王' UNION ALLSELECT '02', N'小陈' UNION ALLSELECT '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_strGOCREATE FUNCTION dbo.f_str(    @id INT)RETURNS VARCHAR(1000)ASBEGIN    DECLARE @r VARCHAR(1000)    SELECT @r = ISNULL(@r+',', '') + name    FROM test    WHERE id = @id    RETURN @rENDGO
--查询
SELECT DISTINCT id,dbo.f_str(id) name from test--结果
/*
id             name
------     -------------
01     小张,小王
02     小王,小陈,张三
03     李四,王武
*/

Answer 7

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

Answer 8

--上边代码不够紧凑又修改了一下如下
--> 生成测试数据表: [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        李四,王武
*/

Answer 9

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 行受影响)
**/