求一条分类汇总的语句解决方法

有表tb, 如下：id    value----- ------1     aa1     bb2     aaa2     bbb2     ccc需要得到结果：id     values------ -----------1      aa,bb2      aaa,bbb,ccc即， group by id, 求 value 的和（字符串相加）1. 旧的解决方法-- 1. 创建处理函数CREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGIN    DECLARE @r varchar(8000)    SET @r = ''    SELECT @r = @r + ',' + value    FROM tb    WHERE [email protected]    RETURN STUFF(@r, 1, 1, '')ENDGO-- 调用函数SELECt id, values=dbo.f_str(id) FROM tb GROUP BY id-- 2. 新的解决方法 -- 示例数据DECLARE @t TABLE(id int, value varchar(10))INSERT @t SELECT 1, 'aa'UNION ALL SELECT 1, 'bb'UNION ALL SELECT 2, 'aaa'UNION ALL SELECT 2, 'bbb'UNION ALL SELECT 2, 'ccc'-- 查询处理SELECT *FROM(    SELECT DISTINCT         id    FROM @t)AOUTER APPLY(    SELECT         [values]= STUFF(REPLACE(REPLACE(            (                SELECT value FROM @t N                WHERE id = A.id                FOR XML AUTO            ), '<N value="', ','), '"/>', ''), 1, 1, ''))N/*--结果id          values----------- ----------------1           aa,bb2           aaa,bbb,ccc(2 行受影响)--*/--各种字符串分函数--3.3.1 使用游标法进行字符串合并处理的示例。--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理--定义结果集表变量DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))--定义游标并进行合并处理DECLARE tb CURSOR LOCALFORSELECT col1,col2 FROM tb ORDER BY  col1,col2DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)OPEN tbFETCH tb INTO @col1,@col2SELECT @[email protected],@s=''WHILE @@FETCH_STATUS=0BEGIN    IF @[email protected]_old        SELECT @[email protected]+','+CAST(@col2 as varchar)    ELSE    BEGIN        INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))        SELECT @s=','+CAST(@col2 as varchar),@[email protected]    END    FETCH tb INTO @col1,@col2ENDINSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))CLOSE tbDEALLOCATE tb--显示结果并删除测试数据SELECT * FROM @tDROP TABLE tb/*--结果col1       col2---------- -----------a          1,2b          1,2,3--*/GO/*==============================================*/--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3GO--合并处理函数CREATE FUNCTION dbo.f_str(@col1 varchar(10))RETURNS varchar(100)ASBEGIN    DECLARE @re varchar(100)    SET @re=''    SELECT @[email protected]+','+CAST(col2 as varchar)    FROM tb    WHERE [email protected]    RETURN(STUFF(@re,1,1,''))ENDGO--调用函数SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1--删除测试DROP TABLE tbDROP FUNCTION f_str/*--结果col1       col2---------- -----------a          1,2b          1,2,3--*/GO/*==============================================*/--3.3.3 使用临时表实现字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理SELECT col1,col2=CAST(col2 as varchar(100)) INTO #t FROM tbORDER BY col1,col2DECLARE @col1 varchar(10),@col2 varchar(100)UPDATE #t SET     @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,    @col1=col1,    [email protected]SELECT * FROM #t/*--更新处理后的临时表col1       col2---------- -------------a          1a          1,2b          1b          1,2b          1,2,3--*/--得到最终结果SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1/*--结果col1       col2---------- -----------a          1,2b          1,2,3--*/--删除测试DROP TABLE tb,#tGO/*==============================================*/--3.3.4.1 每组 <=2 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'c',3--合并处理SELECT col1,    col2=CAST(MIN(col2) as varchar)        +CASE             WHEN COUNT(*)=1 THEN ''            ELSE ','+CAST(MAX(col2) as varchar)        ENDFROM tbGROUP BY col1DROP TABLE tb/*--结果col1       col2      ---------- ----------a          1,2b          1,2c          3--*/--3.3.4.2 每组 <=3 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3UNION ALL SELECT 'c',3--合并处理SELECT col1,    col2=CAST(MIN(col2) as varchar)        +CASE             WHEN COUNT(*)=3 THEN ','                +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)            ELSE ''        END        +CASE             WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)            ELSE ''        ENDFROM tb aGROUP BY col1DROP TABLE tb/*--结果col1       col2---------- ------------a          1,2b          1,2,3c          3--*/GOif not object_id('A') is null    drop table AGoCreate table A([id] int,[cname] nvarchar(2))Insert Aselect 1,N'张三' union allselect 2,N'李四' union allselect 3,N'王五' union allselect 4,N'蔡六'Go--> -->  if not object_id('B') is null    drop table BGoCreate table B([id] int,[cname] nvarchar(5))Insert Bselect 1,N'1,2,3' union allselect 2,N'3,4'Gocreate function F_str(@cname nvarchar(100))returns nvarchar(100)asbegin select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',',[email protected]+',')>0return @cnameendgoselect [id],dbo.F_str([cname])[cname] from Bid          cname----------- ----------------------------------------------------------------1           张三,李四,王五2           王五,蔡六(2 個資料列受到影響)