MSSQL 字符合併

-->Title:Generating test data

-->Author:wufeng4552

-->Date :2009-10-01 15:16:26

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

go

create table tb(ID int ,[name] varchar(10))

insert tb select 1,'A' union all

select 3,'B' union all

select 5,'C' union all

select 7,'D' union all

select 8,'E' union all

select 10,'F' union all

select 15,'G'

;with stone

as

(select *,cnt=row_number()over(order by id)from tb)

select ltrim(ID)+[name]+isnull(','+(select ltrim(ID)+[name]from stone where cnt=t.cnt+1),'')

from stone t where cnt%2=1

go

select ltrim(a.id)+a.[name]+isnull(','+ltrim(b.id)+b.[name],'')

from

(select *,cnt=(select count(*) from tb where id<=t.id) from tb t)a

left join

(select *,cnt=(select count(*) from tb where id<=t.id) from tb t)b

on a.cnt=b.cnt-1

where a.cnt%2=1

/*

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

1A,3B

5C,7D

8E,10F

15G

(4 個資料列受到影響)

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

1A,3B

5C,7D

8E,10F

15G

(4 個資料列受到影響)

*/