How to model unbalanced/rigged hierarchical data - entirely flattened

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:

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

L01         L02                         L03                         L04                                         Name

0_CEO                                                                                                             0_CEO              

0_CEO   1_Director                                                                                       1_Director         

0_CEO   1_Director           11_Manager                                                     11_Manager         

0_CEO   1_Director           12_Manager                                                     12_Manager         

0_CEO   1_Director           13_Manager                                                     13_Manager         

0_CEO   1_Director           12_Manager      121_Worker                         121_Worker         

0_CEO   1_Director           12_Manager      122_Worker                         122_Worker         

0_CEO   1_Director           11_Manager      111_Worker                         111_Worker         

Orphan NULL                     NULL                     NULL                                     Orphan

Comment:

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

This solution is very flexible when modelled in any BI tool, you can use the hierarchy to navigate data as requested.

Solutions below in SQL

*/

DROP TABLE IF EXISTS Employee;

DROP TABLE IF EXISTS EmployeeHier;

DROP TABLE IF EXISTS Salary;

create table Employee (Name varchar(20))

insert Employee values

('0_CEO')

,('1_Director')

,('11_Manager')

,('111_Worker')

,('12_Manager')

,('121_Worker')

,('122_Worker')

,('13_Manager')

,('Orphan');

create table EmployeeHier (ChildName varchar(20),ParentName varchar(20))

insert EmployeeHier values

('1_Director','0_CEO')

,('11_Manager','1_Director')

,('111_Worker','11_Manager')

,('12_Manager','1_Director')

,('121_Worker','12_Manager')

,('122_Worker','12_Manager')

,('13_Manager','1_Director');

create table Salary (Name varchar(20),Income int)

insert into Salary values

('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);

with CTE

     as (select ParentName as ChildName,

                ParentName,

                Cast(isnull(left(ParentName + Replicate(' ', 20), 20), '') as varchar(1000)) FullPath

         from   EmployeeHier

         where  ParentName not in (select distinct ChildName from EmployeeHier)

         union all

         select child.ChildName,

                child.ParentName,

                                                                Cast(CTE.FullPath + isnull(left(child.ChildName+Replicate(' ', 20), 20), '') as varchar(1000)) FullPath

         from   CTE inner join EmployeeHier child on child.ParentName = CTE.ChildName)

select

  rtrim(substring(FullPath, 1,20))  L01

, rtrim(substring(FullPath,21,20))  L02

, rtrim(substring(FullPath,41,20))  L03

, rtrim(substring(FullPath,61,20))  L04

, Name

from (

                                select

                                FullPath

                                ,right(FullPath,20) Name

                                from CTE

)  T

union all

--# Stand alone nodes

select Name -- L01

, null -- L02

, null -- L03

, null -- L04

, name

from Employee

where name not in (       select distinct ChildName from EmployeeHier)

  and name not in ( select distinct ParentName from EmployeeHier );