I happened to solve one business case in my work.
here is my source table
I'd like to TSQL Query in order to get output as follows.
here is my source table
Source | |||
Name | Parent_key | Child_key | Level |
CEO | 1 | 1 | 0 |
Director Finance | 1 | 2 | 1 |
Director Marketting | 1 | 3 | 1 |
Project Manager | 2 | 21 | 3 |
Project Manager | 3 | 31 | 3 |
Team Lead | 21 | 211 | 4 |
Team Lead | 21 | 212 | 4 |
Team Lead | 21 | 213 | 4 |
Victim (me) | 213 | 2131 | 5 |
I'd like to TSQL Query in order to get output as follows.
Expected Output | ||||||
Name | Level0 | Level1 | Level2 | Level3 | Level4 | Level5 |
CEO | 1 | 1 | 1 | 1 | 1 | 1 |
Director Finance | 1 | 2 | 2 | 2 | 2 | 2 |
Director Marketting | 1 | 3 | 3 | 3 | 3 | 3 |
Project Manager | 1 | 2 | 21 | 21 | 21 | 21 |
Project Manager | 1 | 3 | 31 | 31 | 31 | 31 |
Team Lead | 1 | 2 | 21 | 211 | 211 | 211 |
Team Lead | 1 | 2 | 21 | 212 | 212 | 212 |
Team Lead | 1 | 2 | 21 | 213 | 213 | 213 |
Victim (me) | 1 | 2 | 21 | 213 | 2131 | 2131 |
I got the output at different levels by using a CTE & Pivot,
but I struggled a lot to repeat parent levels values to the top most parent. also duplicate the bottom most child to the rest of all levels.
Name | Level0 | Level1 | Level2 | Level3 | Level4 | Level5 |
CEO | 1 | |||||
Director Finance | 2 | |||||
Director Marketting | 3 | |||||
Project Manager | 21 | |||||
Project Manager | 31 | |||||
Team Lead | 211 | |||||
Team Lead | 212 | |||||
Team Lead | 213 | |||||
Victim (me) | 2131 |
but I struggled a lot to repeat parent levels values to the top most parent. also duplicate the bottom most child to the rest of all levels.
hence ended up writing the output in a temporary table and keep updating it.. (though I hate it, I need to do it.)
if you have any better solution, kindly let me know?