CODEWITHSUNDEEP
sqlsql-serverhierarchy
Phh
Phh

Reputation: 135

How to pivot this parent child tables?

I would like to ask for help for pivoting this Parent child table.

My Parent-Child table:

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

I want to pivot the Parent-Child table into this table:

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

However my test query still doesn't work.

my test :

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

I have tested only 4 level, but in the real database, the number of level may change.

Table for test:

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')

Upvotes: 3

Views: 1119

Answers (1)

Luis Cazares
Luis Cazares

Reputation: 3585

The number of columns shouldn't change, so you need to specify a definitive amount of them. You need to change your JOINs to OUTER JOINs to allow having hierarchies with less levels than the maximum.

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);

Upvotes: 1

Related Questions