将父/子表转换为固定列的维表 [英] Transform a parent/child table to a fixed column dimentional table
本文介绍了将父/子表转换为固定列的维表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个关系表(id,parentId,name)
I've a relational table (id, parentId, name)
我想将其转换为扁平的二维表
which I'd like to convert to a flattened dimentional table
(id,Level1,Level2,Level3,Level4)
(id, Level1, Level2, Level3, Level4)
我可以将深度固定为4深.
I'm ok fixing the depth at 4 deep.
我在递归CTE和数据透视方面取得了进步,但结果集不正确
I've made progress with a recursive CTE and pivot, but the result set isn't right
我知道
Id Name Level1 Level2
0 Root NULL NULL
1 NULL L1 NULL
但我需要
Id Name Level1 Level2
0 Root NULL NULL
1 Root L1 NULL
这是我要约会的
with rcte as
(
select h.id
,h.parent_id
,h.name
,1 as HierarchyLevel
FROM RelTable h
where id = 1
union all
select h2.id
, h2.parent_id
, h2.name
, r.HierarchyLevel + 1 AS HierarchyLevel
FROM RelTable h2
inner join rcte r on h2.parent_id = r.id
)
select id, parent_id, [1] as L1,[2] as L2,[3] as L3, [4] as L4
from (
select id,parent_id,name,HierarchyLevel from rcte
) as src
pivot ( max(name) for HierarchyLevel in ([1],[2],[3],[4]) ) as pvt
我在做什么错了?
推荐答案
解决方案过于复杂?如果固定在四个深度,则可以通过一些简单的连接来完成...
Overcomplicating the solution? If it's fixed at four deep then it can be done with some simple joins...
SELECT
L1.id as ID
L1.Name as Level1
L2.Name as Level2
L3.Name as Level3
L4.Name as Level4
FROM
RelTable as L1
INNER JOIN
RelTable as L2
ON L1.id = L2.ParentID
INNER JOIN
RelTable as L3
ON L2.id = L3.ParentID
INNER JOIN
RelTable as L4
ON L3.id = L4.ParentID
使用CTE的练习虽然没有用,但可以满足您的需要.
As an exercise in using CTEs its useless, but it does what you need.
这篇关于将父/子表转换为固定列的维表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文