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

*/