关于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