--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]( [id] int, [UserID] int, [Value] varchar(2) ) insert [test] select 1,100,'AA' union all select 2,100,'BB' union all select 3,101,'CC' union all select 4,100,'DD' union all select 5,101,'EE' SELECT *FROM (SELECT DISTINCT [UserID] FROM [test])A OUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM [test] N WHERE [UserID] = A.[UserID] FOR XML AUTO ), '<N value="', ','), '"/>', ''), 1, 1, '') )N/* UserID values 100 AA,BB,DD 101 CC,EE */
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]( [id] int, [UserID] int, [Value] varchar(2) ) insert [test] select 1,100,'AA' union all select 2,100,'BB' union all select 3,101,'CC' union all select 4,100,'DD' union all select 5,101,'EE'Select Distinct userid,(select value+',' from test b where a.userid=b.userid for xml path('')) theGroup From test a
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[id] int,
[UserID] int,
[Value] varchar(2)
)
insert [test]
select 1,100,'AA' union all
select 2,100,'BB' union all
select 3,101,'CC' union all
select 4,100,'DD' union all
select 5,101,'EE'
SELECT *FROM (SELECT DISTINCT [UserID] FROM [test])A
OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
( SELECT value FROM [test] N
WHERE [UserID] = A.[UserID]
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N/*
UserID values
100 AA,BB,DD
101 CC,EE
*/
if object_id('[test]') is not null
drop table [test]
create table [test](
[id] int,
[UserID] int,
[Value] varchar(2)
)
insert [test]
select 1,100,'AA' union all
select 2,100,'BB' union all
select 3,101,'CC' union all
select 4,100,'DD' union all
select 5,101,'EE'Select Distinct userid,(select value+','
from test b where a.userid=b.userid
for xml path('')) theGroup
From test a