关于SQL Server 中字符串的合并

 体会：

1. 在子查询中，使用聚合函数和 FOR XML PATH('')来确保返回记录是一条。
2. 去重用DISTINCT 和GROUP BY.

 

if

not object_id('Tab') is null

drop table Tab

Go

Create

table Tab([Col1] int,[Col2] nvarchar(1))

Insert

Tab

select

1,N'a' union all

select

1,N'b' union all

select

1,N'c' union all

select

2,N'd' union all

select

2,N'e' union all

select

3,N'f'

Go

SELECT

* FROM Tab;

GO

/******************************************************

Expected Result:

1 a,b,c

2 d,e

3 f

or

1 abc

2 de

3 f

*******************************************************/

/***********************Use XML Method*****************/

--Method 0:

SELECT

Col1

,

Col2=STUFF(

(SELECT ','+Col2 FROM Tab t WHERE t.Col1=Tab.Col1 FOR XML PATH(''))

,1,1,'' )

FROM

Tab

GROUP

BY Col1

GO

SELECT

Col1,Col2=(SELECT ''+Col2 FROM Tab t WHERE t.Col1=Tab.Col1 FOR XML PATH(''))

FROM

Tab

GROUP

BY Col1

 

--Method 1:

select

a

.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')

from

(select distinct COl1 from Tab) a

Cross

apply

(select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b

--Method 2:

select

a

.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))

from

(select distinct COl1 from Tab) a

cross

apply

(select Col2=(select COl2 from Tab where COl1=a.COl1 FOR XML AUTO, TYPE)

.query('<Tab>

{for $i in /Tab[position()<last()]/@COl2 return concat(string($i),",")}

{concat("",string(/Tab[last()]/@COl2))}

</Tab>'

)

)b

 

/*********************Use CTE ******************************/

with

roy

as

(

select Col1,Col2,row=row_number()over(partition by COl1 order by COl1)

from Tab

)

,

Roy2 as

(

select COl1,cast(COl2 as nvarchar(100))COl2,row

from Roy where row=1

union all

select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row

from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1

)

select

Col1,Col2

from

Roy2 a

where

row=(select max(row) from roy where Col1=a.COl1)

order by Col1 option (MAXRECURSION 0)

/********************Use Case*******************************/

SELECT

COl1,

COl2

=''+

CASE

WHEN COUNT(*)=1 THEN CAST(MAX(COl2) as varchar)

ELSE (

SELECT ''+ Col2 FROM Tab t WHERE t.Col1=Tab.Col1 FOR XML PATH('')

)

END

FROM

Tab

GROUP

BY COl1