62,041
社区成员
发帖
与我相关
我的任务
分享
--> Test Data: T
create table T([name] varchar(1),[value] varchar(2))
insert into T
select 'A','a1' union all
select 'A','a2' union all
select 'A','a3' union all
select 'B','b1' union all
select 'B','b2'
--select * from T
go
--Code
--SQL2000用函数
create function f_str
(@a varchar(10))
returns varchar(100)
as
begin
declare @s varchar(100)
select @s = isnull(@s + ',' ,'') +value from T where name = @a
return @s
end
go
select distinct [name],[value]=dbo.f_str([name])
from T
--SQL2005用XML
select distinct [name],
[value]=STUFF((select ','+[value] from T where [name]=A.[name] for XML path('')),1,1,'')
FROM T A group by [name]
--Drop
drop table T
drop function f_str
--Result
/*
name value
---- --------------------------------------------------
A a1,a2,a3
B b1,b2
*/
--> Test Data: @T
declare @T table ([name] varchar(1),[value] varchar(2))
insert into @T
select 'A','a1' union all
select 'A','a2' union all
select 'A','a3' union all
select 'B','b1' union all
select 'B','b2'
--select * from @T
--Code
--SQL2005用XML
select [name],
[value]=STUFF((select ','+[value] from @T where [name]=A.[name] for XML path('')),1,1,'')
FROM @T A group by [name]
--Result
/*
name value
---- --------------------------------------------------
A a1,a2,a3
B b1,b2
*/