按组分类求总值的sql语句(转)

有表tb, 如下：
id     value
----- ------
1      aa
1      bb
2      aaa
2      bbb
2      ccc
需要得到结果：

id      values
------ -----------
1       aa,bb

2       aaa,bbb,ccc
即， group by id, 求 value 的和（字符串相加）
1 . 旧的解决方法
-- 1. 创建处理函数
create function dbo.f_str( @id int )
returns varchar ( 8000 )
as
begin
     declare @r varchar ( 8000 )
     set @r = ''
     select @r = @r + ' , ' + value
     from tb
     where id = @id

     return stuff ( @r , 1 , 1 , '' )
end
go
-- 调用函数
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 ) A

outer 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,bb

2              aaa,bbb,ccc

(2 行受影响)

-- */

-- 各种字符串分函数

-- 3.3.1 使用游标法进行字符串合并处理的示例。

-- 处理的数据

create table tb(col1 varchar ( 10 ), col2 int )

insert   tb select ' a ' , 1

union all select ' a ' , 2

union all select ' b ' , 1

union all select ' b ' , 2

union all select ' b ' , 3

-- 合并处理

-- 定义结果集表变量

declare @t table (col1 varchar ( 10 ),col2 varchar ( 100 ))

-- 定义游标并进行合并处理

declare tb cursor local for

select col1,col2 from tb order by col1,col2

declare @col1_old varchar ( 10 ), @col1 varchar ( 10 ), @col2 int , @s varchar ( 100 )

open tb

fetch tb into @col1 , @col2

select @col1_old = @col1 , @s = ''

while @@fetch_status = 0

begin

     IF @col1 = @col1_old

         select @s = @s + ' , ' +cast ( @col2 as varchar )

     else

     begin

         insert @t values ( @col1_old , stuff ( @s , 1 , 1 , '' ))

         select @s = ' , ' + cast ( @col2 as varchar ), @col1_old = @col1

     end

      fetch tb into @col1 , @col2

end

insert @t values ( @col1_old , stuff ( @s , 1 , 1 , '' ))

close tb

deallocate tb

-- 显示结果并删除测试数据

select * from @t drop table tb

/* --结果

col1       col2

---------- -----------

a           1,2

b           1,2,3

-- */

go

/* ============================================== */

-- 3.3.2 使用用户定义函数，配合select处理完成字符串合并处理的示例

-- 处理的数据

create table tb(col1 varchar ( 10 ),col2 int )

insert   tb select ' a ' , 1

union all select ' a ' , 2

union all select ' b ' , 1

union all select ' b ' , 2

union all select ' b ' , 3

go

-- 合并处理函数

create function dbo.f_str( @col1 varchar ( 10 ))

returns varchar ( 100 )

as

begin

    declare @re varchar ( 100 )

    set @re = ' '

    select @re = @re + ' , ' + cast (col2 as varchar )

    from tb

    where col1 = @col1

    return ( stuff ( @re , 1 , 1 , '' ))

end

go

-- 调用函数

select col1,col2 = dbo.f_str(col1) from tb group by col1

-- 删除测试

drop table tb

drop function f_str

/* --结果

col1        col2

---------- -----------

a            1,2

b            1,2,3

-- */

go

/* ============================================== */

-- 3.3.3 使用临时表实现字符串合并处理的示例 -- 处理的数据

create table tb(col1 varchar ( 10 ),col2 int )

insert   tb select ' a ' , 1

union all select ' a ' , 2

union all select ' b ' , 1

union all select ' b ' , 2

union all select ' b ' , 3

-- 合并处理

select col1,col2 = cast (col2 as varchar ( 100 ))

into #t from tb

order by col1,col2

declare @col1 varchar ( 10 ), @col2 varchar ( 100 )

update #t set

      @col2 = case when @col1 = col1 then @col2 + ' , ' + col2 else col2 end ,

      @col1 = col1,

      col2 = @col2

select * from #t

/* --更新处理后的临时表

col1       col2

---------- -------------

a           1

a           1,2

b           1

b           1,2

b           1,2,3

-- */

-- 得到最终结果

select col1,col2 = max (col2) from #t group by col1

/* --结果

col1       col2

---------- -----------

a           1,2

b           1,2,3

-- */

-- 删除测试

drop table tb,#t go

/* ============================================== */

-- 3.3.4.1 每组 <=2 条记录的合并 -- 处理的数据

create table tb(col1 varchar ( 10 ),col2 int )

insert   tb select ' a ' , 1

union all select ' a ' , 2

union all select ' b ' , 1

union all select ' b ' , 2

union all select ' c ' , 3

-- 合并处理

select col1, col2 = cast ( min (col2) as varchar )

   + case

      when count ( * ) = 1 then ' '

      else ' , ' + cast ( max (col2) as varchar )

   end

from tb

group by col1

drop table tb

/* --结果

col1       col2

---------- ----------

a           1,2

b           1,2

c           3

-- */

转载于:https://www.cnblogs.com/jiajiayuan/archive/2011/07/15/2107042.html