)


--将多行合并成一行，并做分组统计
--001 aa,bb 3
''),[count]
GROUP BY code
FROM test t1


,

--方法二
FROM (SELECT code,sum([count]) as [count]

SELECT code, data=STUFF((SELECT ','+[values] FROM test t WHERE code=t1.code FOR XML PATH('')), 1, 1, ''),sum([count]) as [count]
drop table test
CREATE TABLE test(code varchar(50), [values] varchar(10),[count] int)

复制代码 代码如下:

UNION ALL SELECT '001', 'bb',2
FOR XML PATH(''), ROOT('R'), TYPE
FROM test
UNION ALL SELECT '002', 'ccc',3;
INSERT test SELECT '001', 'aa',1
stuff(b.[values].value('/R[1]', 'nvarchar(max)'),



,
UNION ALL SELECT '002', 'aaa',4

---SQL2005中的新解法 使用XML

--查询结果
SELECT N',' + [values] FROM test
GROUP BY code) a

SELECT [values] =(
--创建 test 表 ，插入数据
[values] =
UNION ALL SELECT '002', 'bbb',5

--方法一
WHERE code = a.code



) b;

--002 aaa,bbb,ccc 12
CROSS apply (
SELECT code,