Monday, February 10, 2014

PIVOT UNPIVOT HIERARCHY repetition of levels issue

I happened to solve one business case in my work.
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, 

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?