如何旋转这个父母表

Question

我想寻求帮助以旋转这张父母的桌子。

我的父子表：

parent  child
ve1      vet12 
ve1      vet13
vet12    adm1
vet13    adm2
adm2     xyz
ve2      vy1
vx       kit1

我想将亲子表旋转到此表中：

level1  level2  level3  level4
 ve1     vet12   adm1
 ve1     vet13   adm2    xyz
 ve2     vy1
 vx      kit1

但是我的测试查询仍然不起作用。

---

我的测试：

SELECT
    L1.child as ID
    L1.parent as Level1
    L2.parent as Level2
    L3.parent as Level3
    L4.parent as Level4
FROM
    Hierarchy as L1
        INNER JOIN
    Hierarchy as L2
        ON L1.child = L2.parent
        INNER JOIN
    Hierarchy as L3
        ON L2.child = L3.parent
        INNER JOIN
    Hierarchy as L4
        ON L3.child = L4.parent

我仅测试了4个级别，但是在实际数据库中，级别的数量可能会改变。

---

测试表：

CREATE TABLE Hierarchy(
    parent  VARCHAR(20),
    child VARCHAR(20))

  INSERT INTO Hierarchy VALUES ('ve1','vet12'),
('ve1' ,'vet13'),
('vet12','adm1'),
('vet13','adm2'),
('adm2','xyz'),
('ve2','vy1'),
('vx','kit1')

Answer 1

列的数量不应更改，因此您需要指定其中的确定数量。您需要更改连接到外部连接，以使层次结构的级别少于最大值。

SELECT
    L1.parent AS Level1,
    L1.child  AS Level2,
    L2.child  AS Level3,
    L3.child  AS Level4,
    L4.child  AS Level5
FROM      Hierarchy as L1
LEFT JOIN Hierarchy as L2 ON L1.child = L2.parent
LEFT JOIN Hierarchy as L3 ON L2.child = L3.parent
LEFT JOIN Hierarchy as L4 ON L3.child = L4.parent
WHERE NOT EXISTS( SELECT Child FROM Hierarchy h WHERE h.child = L1.Parent);