在子查询中，使用聚合函数和 FOR XML PATH('')来确保返回记录是一条。
去重用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
--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
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)
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
责任编辑：小草
