Parent Child relation - Sql query

My source is as follows

OSPID     OSPNAME RELATEDOSPID
10开发者_C百科0004  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

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