sqlserver 分组合并

 --创建 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