MSSQL 使用临时表实现字符串合并处理

-->Title:Generating test data

-->Author:wufeng4552

-->Date :2009-09-30 15:16:26

if object_id('tb')is not null drop table tb

go

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',null

UNION ALL SELECT 'a',null

UNION ALL SELECT 'b',null

UNION ALL SELECT 'b',null

declare @col1 varchar(10)

declare @col2 int

update tb set @col2=case when @col1=col1 then @col2+1 else 1 end,

              @col1=col1,col2=@col2

 

--每组 <=2 条的合并

select col1,

       col2=ltrim(min(col2))+case when count(*)=1 then ''

                             else ','+ltrim(max(col2)) end

from tb group by col1

/*

col1       col2

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

a          1,2

b          1,2

 

*/

--每组 <=3 条的合并

insert tb select 'b',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 ''

  END

from tb a group by col1

/*

col1       col2

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

a          1,2

b          1,2,3

 

*/

      

      

if object_id('tempdb..#')is not null drop table #

go

select col1,

       ltrim(col2)col2

into # from tb order by col1,col2

declare @col1 varchar(10)

declare @col2 varchar(20)

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

             @col1=col1,

             col2=@col2

go

select col1,

       max(col2)col2

from # group by col1

/*

col1       col2

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

a          1,2

b          1,2,3

 

*/