數(shù)據(jù)庫(kù)輔導(dǎo)：SQL中合并分拆表方法匯總

整理的方法如下：
    Title: 在SQL中分類(lèi)合并數(shù)據(jù)行
    Author: dobear Mail(MSN): dobear_0922@hotmail.com
    Environment: Vista + SQL2005
    Date: 20080422
    1. 創(chuàng)建表，添加測(cè)試數(shù)據(jù)
    CREATE TABLE tb(id int, [value] varchar(10))
    INSERT tb SELECT 1, 'aa'
    UNION ALL SELECT 1, 'bb'
    UNION ALL SELECT 2, 'aaa'
    UNION ALL SELECT 2, 'bbb'
    UNION ALL SELECT 2, 'ccc'
    SELECT FROM tb
    ///
    id value
    1 aa
    1 bb
    2 aaa
    2 bbb
    2 ccc
    (5 row(s) affected)
    /
    2 在SQL2000只能用自定義函數(shù)實(shí)現(xiàn)
    2.1 創(chuàng)建合并函數(shù)fn_strSum，根據(jù)id合并value值
    GO
    CREATE FUNCTION dbo.fn_strSum(@id int)
    RETURNS varchar(8000)
    AS
    BEGIN
    DECLARE @values varchar(8000)
    SET @values = ''
    SELECT @values = @values + ',' + value FROM tb WHERE id=@id
    RETURN STUFF(@values, 1, 1, '')
    END
    GO
    調(diào)用函數(shù)
    SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BY id
    DROP FUNCTION dbo.fn_strSum
    2.2 創(chuàng)建合并函數(shù)fn_strSum2，根據(jù)id合并value值
    GO
    CREATE FUNCTION dbo.fn_strSum2(@id int)
    RETURNS varchar(8000)
    AS
    BEGIN
    DECLARE @values varchar(8000)
    SELECT @values = isnull(@values + ',', '') + value FROM tb WHERE id=@id
    RETURN @values
    END
    GO
    調(diào)用函數(shù)
    SELECT id, VALUE = dbo.fn_strSum2(id) FROM tb GROUP BY id
    DROP FUNCTION dbo.fn_strSum2
    3 在SQL2005中的新解法
    3.1 使用OUTER APPLY
    SELECT
    FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY(
    SELECT [values]= STUFF(REPLACE(REPLACE(
    (
    SELECT value FROM tb N
    WHERE id = A.id
    FOR XML AUTO
    ), '', ''), 1, 1, '')
    )N
    3.2 使用XML
    SELECT id, [values]=STUFF((SELECT ','+[value] FROM tb t WHERE id=tb.id FOR XML PATH('')), 1, 1, '')
    FROM tb
    GROUP BY id
    4 刪除測(cè)試表tb
    drop table tb
    ///
    id values
    1 aa,bb
    2 aaa,bbb,ccc
    (2 row(s) affected)
    /
    roy_88 兄弟整理的方法：
    合并分拆表
    /
    合并分拆表數(shù)據(jù)
    整理人：中國(guó)風(fēng)(Roy)
    日期:2008.06.06
    /
    > > (Roy)生成測(cè)試數(shù)據(jù)
    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合并表:
    SQL2000用函數(shù):
    go
    if object_id('F_Str') is not null
    drop function F_Str
    go
    create function F_Str(@Col1 int)
    returns nvarchar(100)
    as
    begin
    declare @S nvarchar(100)
    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
    return @S
    end
    go
    Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab
    go
    SQL2005用XML:
    方法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
    方法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
    SQL05用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)
    生成結(jié)果:
    /
    Col1 COl2
    1 a,b,c
    2 d,e
    3 f
    (3 行受影響)
    /
    拆分表:
    > > (Roy)生成測(cè)試數(shù)據(jù)
    if not object_id('Tab') is null
    drop table Tab
    Go
    Create table Tab([Col1] int,[COl2] nvarchar(5))
    Insert Tab
    select 1,N'a,b,c' union all
    select 2,N'd,e' union all
    select 3,N'f'
    Go
    SQL2000用輔助表:
    if object_id('Tempdb..#Num') is not null
    drop table #Num
    go
    select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
    Select
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)b.ID)
    from
    Tab a,#Num b
    where
    charindex(',',','+a.Col2,b.ID)=b.ID 也可用 substring(','+a.COl2,b.ID,1)=','
    SQL2005用Xml:
    select
    a.COl1,b.Col2
    from
    (select Col1,COl2=convert(xml,''+replace(COl2,',','')+'') from Tab)a
    outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
    SQL05用CTE:
    ;with roy as
    (select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
    union all
    select Col1,COl2=cast(left(Split,charindex(',',Split)1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
    )
    select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)
    生成結(jié)果:
    /
    Col1 COl2
    1 a
    1 b
    1 c
    2 d
    2 e
    3 f
    /