在⼦查询中，使⽤聚合函数和 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
sql server拼接字符串函数
  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('
  {for $i in /Tab[position()  {concat("",string(/Tab[last()]/@COl2))}
  ')
  )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 w+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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。