計(jì)算機(jī)三級(jí):SQLServer中字符串的合并

在子查詢中，使用聚合函數(shù)和 FOR XML PATH('')來(lái)確保返回記錄是一條。
    去重用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('
    {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 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