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)'), , , ''),[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
相关推荐
专注前端开发 2020-10-21
苏康申 2020-11-13
vitasfly 2020-11-12
oraclemch 2020-11-06
liuyang000 2020-09-25
FellowYourHeart 2020-10-05
赵继业 2020-08-17
whyname 2020-08-16
Seandba 2020-08-16
dbasunny 2020-08-16
拼命工作好好玩 2020-08-15
langyue 2020-08-15
写程序的赵童鞋 2020-08-03
Accpcjg 2020-08-02
tydldd 2020-07-30
好记忆也需烂 2020-07-28
jianghero 2020-07-28