sql-server - 从字符串中提取层次结构

Question

我有一个包含两列的表，一列包含代码，另一列包含路径格式:

 Code   Path
 H      A/B/C/G/H
 D      B/L/P/D
 G      A/B/C/G
 R      J/X/R

我的目标是为每个代码提供具有如下级别的父级列表:

Code  Parent  Level
H     A       1
H     B       2
H     C       3
H     G       4
H     H       5
D     B       1
D     L       2
D     P       3
D     D       4
G     A       1
G     B       2
G     C       3
G     G       4
R     J       1
R     X       2
R     R       3

我尝试使用递归代码，但它只显示当前代码的级别。

我如何获得结果？

Answer 1

您可以使用split by xml + cross apply + row_number group by code order by rowrank来做到这一点。

SELECT Code,
    LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Parent,
    row_number() over (partition by code order by (select 1)) AS Level
FROM
(
    SELECT Code,CAST('<XMLRoot><RowData>' 
       + REPLACE(Path,'/','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
    FROM   T
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)