求一个汇总语句（SQL2008）
设有下表：
id UserID Value
1 100 AA
2 100 BB
3 101 CC
4 100 DD
5 101 EE
能否汇总出：
UserID theGroup
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 allselect 2,100,'BB' union allselect 3,101,'CC' union allselect 4,100,'DD' union allselect 5,101,'EE'SELECT *FROM (SELECT DISTINCT [UserID] FROM [test])AOUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM [test] N WHERE [UserID] = A.[UserID] FOR XML AUTO ), '<N value="', ','), '"/>', ''), 1, 1, ''))N/*UserID values100 AA,BB,DD101 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