Parent Child relation - Sql query

Question

My source is as follows

OSPID     OSPNAME RELATEDOSPID
100004  LEVEL4  100003
100003  LEVEL3  100002
100002  LEVEL2  100001
100001  LEVEL1  0
100009  LEVEL4  100008
100008  LEVEL2  100007

I need the result as

L4OSPID L4OSPNAME   L3RELATEDID L3OSPNAME   L2RELATEDID L2OSPNAME   L1RELATEDID L2OSPNAME   ROOTNODE
100004   LEVEL4      100003      LEVEL3      100002      LEVEL2      100001      LEVEL1        0
100009   LEVEL4      100008      NULL        NULL        LEVEL2      100007      NULL         NULL

If any level in between is missing should put NULL

Thanks

Answer 1

Assuming there are only 4 LEVEL and that you always want to start from LEVEL4, here is the query

select L4.OSPID as L4OSPID, L4.OSPNAME as L4OPSNAME,
L4.RELATEDOSPID
 as L3RELATEDID,

case 
    WHEN L3.OSPNAME = 'LEVEL3' THEN L3.OSPNAME
    ELSE NULL
end as L3OSPNAME,

case 
    WHEN L3.OSPNAME = 'LEVEL3' THEN L3.RELATEDOSPID
    ELSE NULL
end as L2RELATEDID,

case 
    WHEN L3.OSPNAME = 'LEVEL2' THEN L3.OSPNAME
    WHEN L2.OSPNAME = 'LEVEL2' THEN L2.OSPNAME
    ELSE NULL
end as L2OSPNAME,

case WHEN L3.OSPNAME = 'LEVEL2' THEN  L3.RELATEDOSPID
    WHEN L2.OSPNAME = 'LEVEL2' THEN  L2.RELATEDOSPID
    ELSE NULL
end
 as L1RELATEDID,

case 
    WHEN L3.OSPNAME = 'LEVEL1' THEN L3.OSPNAME
    WHEN L2.OSPNAME = 'LEVEL1' THEN L2.OSPNAME
    WHEN L1.OSPNAME = 'LEVEL1' THEN L1.OSPNAME
    ELSE NULL
end as L1OSPNAME,

case 
    WHEN L3.OSPNAME = 'LEVEL1' THEN L3.RELATEDOSPID
    WHEN L2.OSPNAME = 'LEVEL1' THEN L2.RELATEDOSPID
    WHEN L1.OSPNAME = 'LEVEL1' THEN L1.RELATEDOSPID
    ELSE NULL
end as ROOTNODE


from osptable as L4

left outer join osptable as L3
on L4.RELATEDOSPID = L3.OSPID
left outer join osptable as L2
on L3.RELATEDOSPID = L2.OSPID
left outer join osptable as L1
on L2.RELATEDOSPID = L1.OSPID
where L4.OSPNAME = 'LEVEL4'

This gave me this result, which i think is the one desired

100004  LEVEL4  100003  LEVEL3  100002  LEVEL2  100001  LEVEL1  0
100009  LEVEL4  100008  NULL    NULL    LEVEL2  100007  NULL    NULL

Answer 2

;with cte AS(
SELECT OSPID as Leaf, OSPID, OSPNAME, RELATEDOSPID
FROM DATA
WHERE OSPNAME = 'LEVEL4'
UNION ALL
SELECT c.Leaf as Leaf, d.OSPID, d.OSPNAME, d.RELATEDOSPID
FROM DATA d
JOIN cte c ON c.RELATEDOSPID = d.OSPID
)
SELECT 
MAX(CASE WHEN OSPNAME='LEVEL4' THEN OSPID END) L4OSPID,
'LEVEL4' AS L4OSPNAME,
MAX(CASE WHEN OSPNAME='LEVEL3' THEN OSPID END) L3RELATEDID,
'LEVEL3' AS L3OSPNAME,
MAX(CASE WHEN OSPNAME='LEVEL2' THEN OSPID END) L2RELATEDID,
'LEVEL2' AS L2OSPNAME,
MAX(CASE WHEN OSPNAME='LEVEL1' THEN OSPID END) L1RELATEDID,
'LEVEL1' AS L1OSPNAME,
MAX(CASE WHEN OSPNAME='LEVEL1' THEN RELATEDOSPID END) ROOTNODE
FROM cte
GROUP BY Leaf

Returns a slightly different result from your expected. So might need to be tweaked. I don't really see why 100008 would end up in a column entitled L3RELATEDID.

L4OSPID     L4OSPNAME L3RELATEDID L3OSPNAME L2RELATEDID L2OSPNAME L1RELATEDID L1OSPNAME ROOTNODE
----------- --------- ----------- --------- ----------- --------- ----------- --------- -----------
100004      LEVEL4    100003      LEVEL3    100002      LEVEL2    100001      LEVEL1    0
100009      LEVEL4    NULL        LEVEL3    100008      LEVEL2    NULL        LEVEL1    NULL