如何从父子层次结构表创建查询 [英] How to create query from parent child hierarchy table
问题描述
如何编写查询以将具有父/子层次结构的表转换为具有单独列的层次结构级别的表?
How do I write my query to translate a table with parent / child hierarchy into a table with my hierarchy levels in separate columns?
我在 SQL Server 中有一个表(来自 SAP，我相信没有进行任何更改)，它为我提供了包含利润中心的组结构.表的结构是经典的父子层次结构，如下所示.
I have a table in SQL Server (from SAP without any changes made I believe) that gives me the structure of groups containing my profit centers. The structure of the table is a classic parent child hierarchy as shown below.
Parent | Child
--------+--------
S-1 | S-11
S-1 | S-12
S-1 | S-13
S-1 | S-14
S-1 | S-15
S-11 | S-111
S-11 | S-112
.. | ..
S-152 | S-1521
S-152 | S-1522
S-1522 | S-15221
我想编写一个查询，为我提供一个表格，我可以在其中找到每个组的级别 1、级别 2、级别 3 等.团体.级别 1 是最高级别(并将始终存在)，级别 2 是下一个级别.可以有无限的级别，但此时级别 8 是最高使用的.
I want to write a query that gives me a table where I for each group can find the Level 1, Level 2, Level 3 ect. group. Level 1 is the Top Level (and will always exist) and Level 2 the next. There can be unlimited levels but at this time Level 8 is the highest used.
Group | Level 1 | Level 2 | Level 3 | Level 4 | Level 5
--------+-----------+-----------+-----------+-----------+---------
S-111 | S-1 | S-11 | S-111 | |
S-11211 | S-1 | S-11 | S-112 | S-1121 | S-11211
S-1211 | S-1 | S-12 | S-121 | S-1211 |
S-1212 | S-1 | S-12 | S-121 | S-1212 |
S-122 | S-1 | S-12 | S-122 | |
S-123 | S-1 | S-12 | S-123 | |
S-1311 | S-1 | S-13 | S-131 | S-1311 |
S-1312 | S-1 | S-13 | S-131 | S-1312 |
S-1321 | S-1 | S-13 | S-132 | S-1321 |
S-141 | S-1 | S-14 | S-141 | |
S-151 | S-1 | S-15 | S-151 | |
S-1521 | S-1 | S-15 | S-152 | S-1521 |
S-15221 | S-1 | S-15 | S-152 | S-1522 | S-15221
我已使用 Google 和此页面找到最终解决方案，但尚未找到.但我设法做到了这一点:
I have used Google and this page to find the final solutions but haven’t found it yet. But I managed to get this far:
WITH MyTest as
(
SELECT
P.PRCTR_CHILD, P.PRCTR_PARENT,
CAST(P.PRCTR_CHILD AS VARCHAR(MAX)) AS Level
FROM
[IBM_PA_Integration].[dbo].[PRCTRHIER] AS P
WHERE
P.PRCTR_PARENT = 'S-1000' –- S-1000 is a division
UNION ALL
SELECT
P1.PRCTR_CHILD, P1.PRCTR_PARENT,
CAST(P1.PRCTR_CHILD AS VARCHAR(MAX)) + ', ' + M.Level
FROM
[IBM_PA_Integration].[dbo].[PRCTRHIER] AS P1
INNER JOIN
MyTest M ON M.PRCTR_CHILD = P1.PRCTR_PARENT
)
SELECT *
FROM MyTest
WHERE PRCTR_PARENT = 'FS2004' –- FS2004 is the level top level / level above S-1000
推荐答案
如果您的级别数量固定或有限，您可能不需要 DYNAMIC SQL.解析"路径可以用一点XML来完成.
If you have a fixed or limited number of levels, you may not need DYNAMIC SQL. "Parsing" the path can be accomplished with a little XML.
考虑以下事项:
示例:
Declare @YourTable Table ([Parent] varchar(50),[Child] varchar(50))
Insert Into @YourTable Values
(null ,'S-1')
,('S-1','S-11')
,('S-1','S-12')
,('S-1','S-13')
,('S-1','S-14')
,('S-1','S-15')
,('S-11','S-111')
,('S-11','S-112')
;with cteP as (
Select Child
,Parent
,PathID = cast(Child as varchar(500))
From @YourTable
Where Parent is Null
Union All
Select Child = r.Child
,Parent = r.Parent
,PathID = cast(p.PathID+','+cast(r.Child as varchar(25)) as varchar(500))
From @YourTable r
Join cteP p on r.Parent = p.Child)
Select [Group] = Child
,B.*
From cteP A
Cross Apply (
Select Level1 = xDim.value('/x[1]','varchar(max)')
,Level2 = xDim.value('/x[2]','varchar(max)')
,Level3 = xDim.value('/x[3]','varchar(max)')
,Level4 = xDim.value('/x[4]','varchar(max)')
,Level5 = xDim.value('/x[5]','varchar(max)')
From (Select Cast('<x>' + replace(PathID,',','</x><x>')+'</x>' as xml) as xDim) as X
) B
Order By PathID
退货
这篇关于如何从父子层次结构表创建查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
