你的位置:首页 > 数据库

[数据库]FOR XML PATH 应用及其反向分解


数据库环境:SQL SERVER 2005

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

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

图1             图2

1.图1到图2的FOR

  网上有很多介绍FOR

--数据准备;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             '')            ) AS ty       FROM   x0 x1       GROUP BY id      ) t

View Code

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

View Code