树形查询扩展

数据库环境：SQL SERVER 2005

　　有一层次表的数据如下图1，要实现图2的效果，即将层次结构的路径按行显示，

第一列显示的是叶子节点，后面则是分枝节点，最后是根节点。

　　要解决的问题：

　　1.递归实现树形查询

      2.从根节点到叶子节点编成一组

　　3.动态行转列

　　好了，我们依次来解决这些问题。

　　0.建表，导入基础数据

WITH x0 AS(SELECT 1 AS id,'1xx' AS name,2 AS fatherID UNION ALLSELECT 2 AS id,'2xx' AS name,3 AS fatherID UNION ALLSELECT 3 AS id,'3xx' AS name,0 AS fatherID UNION ALLSELECT 4 AS id,'4xx' AS name,3 AS fatherID UNION ALLSELECT 0 AS id,'0xx' AS name,NULL AS fatherID)SELECT * INTO #t FROM x0

　　1.递归分组

　　分组的组号依据叶子节点的id而定，从叶子节点追溯到根节点，它们属于同一组。

/*递归实现层次查询*/WITH  x0 ( id, name, fatherID, way, level )     AS ( SELECT  id ,            name ,            fatherID ,            CONVERT(VARCHAR(50), CAST(id AS VARCHAR(2))) AS way ,            id AS level        FROM   #t t1        /*从叶子节点开始递归*/        WHERE  NOT EXISTS ( SELECT NULL                   FROM  #t t2                   WHERE t2.fatherID = t1.id )        UNION ALL        SELECT  t1.id ,            t1.name ,            t1.fatherID ,            CONVERT(VARCHAR(50), CAST(t1.id AS VARCHAR(2)) + '->'            + t2.way) AS way ,--路径            t2.level--组别        FROM   #t t1 ,            x0 t2        WHERE  t1.id = t2.fatherID       )

　　层次查询的结果如图：

　　2.动态行转列实现

　　有了组号，我们则根据同一组号的数据转到一行上展示。这里需要注意行转列的展示顺序，

排在前面的是子节点，后面是父节点。关于动态行转列的实现，可参考我前面的博客 将部分相同的多行记录转成一行多列。

SELECT id ,      name ,      level ,      ROW_NUMBER() OVER ( PARTITION BY level ORDER BY LEN(way) ) AS rn--节点的展示序号  INTO  #t1  FROM  x0  DECLARE @sql NVARCHAR(MAX);  SET @sql = N'';SELECT @sql = @sql + ',max(case rn when ' + CAST(tt.rn AS VARCHAR)    + ' then id end) ' + CASE WHEN rn = 1 THEN +'id'                 ELSE 'faterID'               END + ',max(case rn when '    + CAST(tt.rn AS VARCHAR) + ' then name end) '    + CASE WHEN rn = 1 THEN +'name'        ELSE 'faterName'     ENDFROM  ( SELECT DISTINCT          rn     FROM   #t1    ) ttORDER BY rn;SET @sql = N'select level' + @sql + ' from #t1 group by level';EXEC(@sql);

（本文完）

原标题：树形查询扩展

关键词：