FOR XML PATH 应用及其反向分解

数据库环境：SQL SERVER 2005

　　我们实现将同一组的数据内容合并到一行的时候，可以通过FOR XML PATH来实现。

有数据如图1，要实现图2的效果

1.图1到图2的FOR XML PATH实现

　　网上有很多介绍FOR XML的方法，这里不再细说，感兴趣的朋友可以去查询一下它的用法。

--数据准备;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             )     /*实现*/    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的递归实现

　　从图2到图1，实现的方法不止递归一种方法，各位可以试着用其它方法解决。

/*准备数据*/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