數(shù)據(jù)庫環(huán)境：SQL SERVER 2005

　　我們實(shí)現(xiàn)將同一組的數(shù)據(jù)內(nèi)容合并到一行的時(shí)候，可以通過FOR XML PATH來實(shí)現(xiàn)。

有數(shù)據(jù)如圖1，要實(shí)現(xiàn)圖2的效果

1.圖1到圖2的FOR XML PATH實(shí)現(xiàn)

　　網(wǎng)上有很多介紹FOR XML的方法，這里不再細(xì)說，感興趣的朋友可以去查詢一下它的用法。

--數(shù)據(jù)準(zhǔn)備;WITH    x0          AS ( SELECT   1 AS id ,                        '001' AS ty               UNION ALL               SELECT   1 AS id ,                        '002' AS ty               UNION ALL               SELECT   2 AS id ,                        '003' AS ty               UNION ALL               SELECT   3 AS id ,                        '004' AS ty               UNION ALL               SELECT   3 AS id ,                        '1234' AS ty               UNION ALL               SELECT   4 AS id ,                        '01' AS ty               UNION ALL               SELECT   4 AS id ,                        '005' AS ty               UNION ALL               SELECT   4 AS id ,                        '006' AS ty             )     /*實(shí)現(xiàn)*/    SELECT  id ,            STUFF(ty, 1, 1, '') AS ty    FROM    ( SELECT    id ,                        ( SELECT    ',' + x2.ty                          FROM      x0 x2                          WHERE     x2.id = x1.id                        FOR                          XML PATH('')                        ) AS ty              FROM      x0 x1              GROUP BY  id            ) t

2.圖2到圖1的遞歸實(shí)現(xiàn)

　　從圖2到圖1，實(shí)現(xiàn)的方法不止遞歸一種方法，各位可以試著用其它方法解決。

/*準(zhǔn)備數(shù)據(jù)*/WITH    x0          AS ( SELECT   1 AS id ,                        '001,002' AS ty               UNION ALL               SELECT   2 AS id ,                        '003' AS ty               UNION ALL               SELECT   3 AS id ,                        '004,1234' AS ty               UNION ALL               SELECT   4 AS id ,                        '01,005,006' AS ty             ),        x1 ( id, ty1, ty2 )          AS ( SELECT   id ,                        CASE WHEN CHARINDEX(',', ty, 1) > 0                             THEN CONVERT(VARCHAR(10), LEFT(ty,                                                            CHARINDEX(',', ty,                                                              1) - 1))                             ELSE ty                        END AS ty1 ,--本次拆分字符                        CASE WHEN CHARINDEX(',', ty, 1) > 0 THEN                              STUFF(ty + ',', 1, CHARINDEX(',', ty), '')                             ELSE NULL                        END AS ty2--待拆分字符串               FROM     x0               UNION ALL               SELECT   id ,                        CONVERT(VARCHAR(10), LEFT(ty2,                                                  NULLIF(CHARINDEX(',', ty2, 1),                                                         0) - 1)) AS ty1 ,--本次拆分字符                        STUFF(ty2, 1, CHARINDEX(',', ty2), '') AS ty2--待拆分字符串               FROM     x1               WHERE    CHARINDEX(',', ty2, 1) > 0             )    SELECT  id,ty1 AS ty    FROM    x1 ORDER BY id