SQL递归查询行转列

由于工作环境中需要在SQL环境中实现树表到节点分级表的转换，引用链接中实例链接在此

   #Categorytree表：

ID	ParentID	CategoryID
1	0	1
2	0	2
3	1	3
4	3	4
5	2	7

 

#Category表：

CategoryID	CategoryName	LevelID
1	企划	1
2	开发	1
3	年度计划	2
4	项目A	3
7	模块开发	2

#Categorylevel表：

LevelID	CategorylevelName
1	分类一
2	分类二
3	分类三

对于递归查询以及行转列的问题在CSDN有很多大佬答疑，但是运行出的结果确不能达到版主想要的如下效果：

 

部门树图

 从以上的逻辑结构树以及最初拥有的3张原始表，得到以下的节点分级表：

rid	分类一	分类二	分类三
1	企划	NULL	NULL
2	开发	NULL	NULL
3	企划	年度计划	NULL
4	企划	年度计划	项目A
5	开发	模块开发	NULL

 

 

在此感谢原回答中两位大佬 chen357313771，排山倒海呦提供思路，根据两位给出的答案（虽然不能得到最终结果）修正后能够实现以上效果的代码如下：

IF OBJECT_ID('tempdb..#Categorytree','U') IS NOT NULL DROP TABLE #Categorytree
CREATE TABLE #Categorytree
(
    ID             INT
    ,ParentID     INT
    ,CategoryID     INT
)
 
INSERT INTO #Categorytree
SELECT 1, 0, 1 UNION ALL 
SELECT 2, 0, 2 UNION ALL
SELECT 3, 1, 3 UNION ALL
SELECT 4, 3, 4 UNION ALL
SELECT 5, 2, 7

IF OBJECT_ID('tempdb..#Categorytree','U') IS NOT NULL DROP TABLE #Category
CREATE TABLE #Category
(
    CategoryID        INT
    ,CategoryName    NVARCHAR(20)
    ,LevelID        INT
)
 
INSERT INTO #Category
SELECT 1, N'企划', 1 UNION ALL
SELECT 2, N'开发', 1 UNION ALL
SELECT 3, N'年度计划', 2 UNION ALL
SELECT 4, N'项目A', 3 UNION ALL
SELECT 7, N'模块开发', 2 
 
IF OBJECT_ID('tempdb..#Categorytree','U') IS NOT NULL DROP TABLE #Categorylevel
CREATE TABLE #Categorylevel
(
    LevelID                INT
    ,CategorylevelName    NVARCHAR(20)
)
 
 
INSERT INTO #Categorylevel
SELECT 1,N'分类一' UNION ALL
SELECT 2,N'分类二' UNION ALL
SELECT 3,N'分类三'
 
;WITH  CTE2
AS (
    SELECT CategoryID as ID,ID as rid
    FROM #Categorytree
    UNION ALL 
    SELECT ParentID,A.rid
    FROM CTE2 AS A
    JOIN #Categorytree AS B ON A.ID=B.CategoryID
    WHERE ParentID>0
) 
, CTE3
AS
(
SELECT A.*, C.CategorylevelName,B.CategoryName
FROM CTE2 AS A
JOIN #Category AS B ON A.ID=B.CategoryID
JOIN #Categorylevel AS C ON B.LevelID=C.LevelID
)

select rid,[分类一],[分类二],[分类三] 
from (select rid,CategorylevelName,CategoryName from CTE3) as src
pivot
(
max(CategoryName)
for CategorylevelName in([分类一],[分类二],[分类三])
) as p

 思路为，递归出所有节点父节点表CTE2，再给CTE2里面的所有父节点赋上对应的名字和所在层级到CTE3，再对CTE3表的原始id（rid）的所有父节点做层级分类得到最终结果如效果图中所展示的一致。

项目（fwE9）内实现：

IF OBJECT_ID('tempdb..#Categorytree','U') IS NOT NULL DROP TABLE #Categorytree;
IF OBJECT_ID('tempdb..#Category','U') IS NOT NULL DROP TABLE #Category;
IF OBJECT_ID('tempdb..#Categorylevel','U') IS NOT NULL DROP TABLE #Categorylevel;
create table #Categorytree
(
    ID             INT
    ,ParentID     INT
    ,CategoryID     INT
	,tempLevel    INT
)
insert into #Categorytree(ID,ParentID,CategoryID)  select id,supdepid,id from HrmDepartment

CREATE TABLE #Category
(
    CategoryID        INT
    ,CategoryName    NVARCHAR(20)
    ,LevelID        INT
)
insert into #Category select id,departmentname,0 from HrmDepartment

CREATE TABLE #Categorylevel
(
    LevelID                INT
    ,CategorylevelName    NVARCHAR(20)
)
INSERT INTO #Categorylevel
SELECT 1,N'一级部门' UNION ALL
SELECT 2,N'二级部门' UNION ALL
SELECT 3,N'三级部门' UNION ALL
SELECT 4,N'四级部门' UNION ALL
SELECT 5,N'五级部门' UNION ALL
SELECT 6,N'六级部门' UNION ALL
SELECT 7,N'七级部门' UNION ALL
SELECT 8,N'八级部门' 

--更新#Category 中的LevelID
update #Categorytree set tempLevel=1 where ParentID=0
while exists (select * from #Categorytree where tempLevel is NULL)
BEGIN
update T set T.tempLevel=P.tempLevel + 1
from #Categorytree as T inner join #Categorytree as P
on (T.ParentID=P.CategoryID) 
where P.tempLevel >= 1 and T.tempLevel is null
END
update A set A.LevelID=B.tempLevel from #Category as A inner join #Categorytree B on A.CategoryID=B.CategoryID

;WITH  CTE2
AS (
    SELECT CategoryID as ID,ID as rid
    FROM #Categorytree
    UNION ALL 
    SELECT ParentID,A.rid
    FROM CTE2 AS A
    JOIN #Categorytree AS B ON A.ID=B.CategoryID
    WHERE ParentID>0
) 
, CTE3
AS
(
SELECT A.*, C.CategorylevelName,B.CategoryName
FROM CTE2 AS A
JOIN #Category AS B ON A.ID=B.CategoryID
JOIN #Categorylevel AS C ON B.LevelID=C.LevelID
)

select rid,[一级部门],[二级部门],[三级部门],[四级部门],[五级部门],[六级部门],[七级部门],[八级部门]
from (select rid,CategorylevelName,CategoryName from CTE3) as src
pivot
(
max(CategoryName)
for CategorylevelName in([一级部门],[二级部门],[三级部门],[四级部门],[五级部门],[六级部门],[七级部门],[八级部门])
) as p