Sql Server:多行合并成一行,并做分组统计
--创建 test 表 ,插入数据
CREATE TABLE test(code varchar(50), [values] varchar(10),[count] int)
INSERT test SELECT '001', 'aa',1
UNION ALL SELECT '001', 'bb',2
UNION ALL SELECT '002', 'aaa',4
UNION ALL SELECT '002', 'bbb',5
UNION ALL SELECT '002', 'ccc',3;
--方法一
--将多行合并成一行,并做分组统计
SELECT code,
[values] =
stuff(b.[values].value('/R[1]', 'nvarchar(max)'),
1,
1,
''),[count]
FROM (SELECT code,sum([count]) as [count]
FROM test
GROUP BY code) a
CROSS apply (
SELECT [values] =(
SELECT N',' + [values] FROM test
WHERE code = a.code
FOR XML PATH(''), ROOT('R'), TYPE
)
) b;
--方法二
---SQL2005中的新解法 使用XML
SELECT code, data=STUFF((SELECT ','+[values] FROM test t WHERE code=t1.code FOR XML PATH('')), 1, 1, ''),sum([count]) as [count]
FROM test t1
GROUP BY code
--查询结果
--001 aa,bb 3
--002 aaa,bbb,ccc 12
drop table test
CREATE TABLE test(code varchar(50), [values] varchar(10),[count] int)
INSERT test SELECT '001', 'aa',1
UNION ALL SELECT '001', 'bb',2
UNION ALL SELECT '002', 'aaa',4
UNION ALL SELECT '002', 'bbb',5
UNION ALL SELECT '002', 'ccc',3;
--方法一
--将多行合并成一行,并做分组统计
SELECT code,
[values] =
stuff(b.[values].value('/R[1]', 'nvarchar(max)'),
1,
1,
''),[count]
FROM (SELECT code,sum([count]) as [count]
FROM test
GROUP BY code) a
CROSS apply (
SELECT [values] =(
SELECT N',' + [values] FROM test
WHERE code = a.code
FOR XML PATH(''), ROOT('R'), TYPE
)
) b;
--方法二
---SQL2005中的新解法 使用XML
SELECT code, data=STUFF((SELECT ','+[values] FROM test t WHERE code=t1.code FOR XML PATH('')), 1, 1, ''),sum([count]) as [count]
FROM test t1
GROUP BY code
--查询结果
--001 aa,bb 3
--002 aaa,bbb,ccc 12
drop table test
![](http://images0.cnblogs.com/blog/338465/201302/21100904-173677be283d41f1891c23f504ebd517.bmp)
作者:阿笨
【官方QQ一群:跟着阿笨一起玩NET(已满)】:422315558
【官方QQ二群:跟着阿笨一起玩C#(已满)】:574187616
【官方QQ三群:跟着阿笨一起玩ASP.NET(已满)】:967920586
【官方QQ四群:Asp.Net Core跨平台技术开发(可加入)】:829227829
【官方QQ五群:.NET Core跨平台开发技术(可加入)】:647639415
【网易云课堂】:https://study.163.com/provider/2544628/index.htm?share=2&shareId=2544628
【腾讯课堂】:https://abennet.ke.qq.com
【51CTO学院】:https://edu.51cto.com/sd/66c64
【微信公众号】:微信搜索:跟着阿笨一起玩NET