How to model unbalanced/rigged hierarchical data - vertically extended

Check list of all posts

The sample code below could explain the concept in detail. Please note that this code is SQL Server based.

/*

Input:

--------------------------------------------------------------------------------------------------

0_CEO                                                                   $20

                1_Director                                           $10

                                11_Manager                      $5

                                                111_Worker       $3

                                12_Manager                      $5

                                                121_Worker       $3

                                                122_Worker       $3

                                13_Manager                      $5

Orphan                                                                 $1

Output:

--------------------------------------------------------------------------------------------------

0_CEO                   0_CEO                   1

0_CEO                   1_Director           2

0_CEO                   11_Manager      3

0_CEO                   111_Worker       4

0_CEO                   12_Manager      3

0_CEO                   121_Worker       4

0_CEO                   122_Worker       4

0_CEO                   13_Manager      3

1_Director           1_Director           1

1_Director           11_Manager      2

1_Director           111_Worker       3

1_Director           12_Manager      2

1_Director           121_Worker       3

1_Director           122_Worker       3

1_Director           13_Manager      2

11_Manager      11_Manager      1

11_Manager      111_Worker       2

111_Worker       111_Worker       1

12_Manager      12_Manager      1

12_Manager      121_Worker       2

12_Manager      122_Worker       2

121_Worker       121_Worker       1

122_Worker       122_Worker       1

13_Manager      13_Manager      1

Orphan                 Orphan                 1

Solutions

Comment:

--------------------------------------------------------------------------------------------------

This solution is very flexible when modelled in any BI tool, you can get  you can get total salary for each individual person and its decedents. For example, select 0_CEO, you will the total salary for whole company accept orphan. Select 11_Manager, you will get total salary of 11_Manager and 111_Worker. 

select H.ChildName, H.ParentName, 2 as LevelFromTop from EmployeeHier H

union ALL

select  CTE.ChildName, H.ParentName, LevelFromTop + 1 as LevelFromTop

from EmployeeHier H

inner join CTE on H.ChildName = CTE.ParentName

where H.ChildName is not NULL )

select 

 T.ParentName

,T.ChildName

,T.LevelFromTop

from

(

                select distinct M.Name as ChildName, M.Name as ParentName, 1 as LevelFromTop from Employee M

                union ALL

                select distinct ChildName, ParentName, LevelFromTop from CTE

) T

inner join Employee M ON (T.ChildName = M.Name)

UNION ALL

select Name,Name,0

from Employee

where Name not in (select ChildName from EmployeeHier)

and  Name not in (select ParentName from EmployeeHier)

order by ParentName, ChildName, LevelFromTop