How to model unbalanced/rigged hierarchical data - entirely flattened from bottom node only

Check list of all posts

The sample code below could explain the concept in detail. Please note that this code is SQL Server based. There are three options provided below.

/*

Input:

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

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     1_Director           11_Manager      NULL               111_Worker

0_CEO     1_Director           12_Manager      NULL               121_Worker

0_CEO     1_Director           12_Manager      NULL               122_Worker

0_CEO     1_Director           NULL                   NULL               13_Manager

Orphan    NULL                    NULL                   NULL               Orphan

Solutions

Comment:

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

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

However, if there are any data related with parent level, then the report won’t be right at all. In this case, any salary from 0_CEO, 1_Director, 11_Manager, 12_Manager won’t be counted

-- Solution Option 1:

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

select

  case when rtrim(substring(FullPath, 1,20)) = Name or len(rtrim(substring(FullPath, 1,20))) = 0 then NULL else rtrim(substring(FullPath, 1,20)) end L01

, case when rtrim(substring(FullPath,21,20)) = Name or len(rtrim(substring(FullPath,21,20))) = 0 then NULL else rtrim(substring(FullPath,21,20)) end L02

, case when rtrim(substring(FullPath,41,20)) = Name or len(rtrim(substring(FullPath,41,20))) = 0 then NULL else rtrim(substring(FullPath,41,20)) end L03

, case when rtrim(substring(FullPath,61,20)) = Name or len(rtrim(substring(FullPath,61,20))) = 0 then NULL else rtrim(substring(FullPath,61,20)) end L04

, Name

from (

                                select

                                isnull(left(h04.ParentName+replicate(' ',20),20),'')+isnull(left(h03.ParentName+replicate(' ',20),20),'')+isnull(left(h02.ParentName+replicate(' ',20),20),'')+isnull(left(h01.ParentName+replicate(' ',20),20)+left(h01.ChildName+replicate(' ',20),20),'')  FullPath

                                ,isnull(isnull(isnull(isnull(h01.ChildName, h01.ParentName), h02.ParentName), h03.ParentName), h04.ParentName) Name

                                from EmployeeHier h01

                                left join EmployeeHier h02 on (h01.ParentName = h02.ChildName)

                                left join EmployeeHier h03 on (h02.ParentName = h03.ChildName)

                                left join EmployeeHier h04 on (h03.ParentName = h04.ChildName)

                                where h01.ChildName not in ( select distinct ParentName from EmployeeHier)

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

-- Solution Option 2:

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

With L04 as

(

                select ParentName as L04, ChildName as Name

                from EmployeeHier

                where ChildName not in ( select distinct ParentName from EmployeeHier)

)

, L03 as

(

                select EmployeeHier.ParentName as L03,L04, Name

                from L04 left join EmployeeHier on (L04.L04=EmployeeHier.ChildName)

)

, L02 as

(

                select EmployeeHier.ParentName as L02,L03,L04, Name

                from L03 left join EmployeeHier on (L03.L03=EmployeeHier.ChildName)

)

, L01 as

(

                select EmployeeHier.ParentName as L01,L02,L03,L04, Name

                from L02 left join EmployeeHier on (L02.L02=EmployeeHier.ChildName)

)

select

  case when rtrim(substring(FullPath, 1,20)) = Name or len(rtrim(substring(FullPath, 1,20))) = 0 then NULL else rtrim(substring(FullPath, 1,20)) end L01

, case when rtrim(substring(FullPath,21,20)) = Name or len(rtrim(substring(FullPath,21,20))) = 0 then NULL else rtrim(substring(FullPath,21,20)) end L02

, case when rtrim(substring(FullPath,41,20)) = Name or len(rtrim(substring(FullPath,41,20))) = 0 then NULL else rtrim(substring(FullPath,41,20)) end L03

, case when rtrim(substring(FullPath,61,20)) = Name or len(rtrim(substring(FullPath,61,20))) = 0 then NULL else rtrim(substring(FullPath,61,20)) end L04

, Name

from

(

                select

                                isnull(left(L01+replicate(' ',20),20),'')+isnull(left(L02+replicate(' ',20),20),'')+isnull(left(L03+replicate(' ',20),20),'')++isnull(left(L04+replicate(' ',20),20),'') FullPath

                                ,Name

                from L01

) T

union all

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

-- Solution Option 3:

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

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 FullPath into #FullPath from CTE

delete t1

from #FullPath t1

inner join #FullPath t2

on t2.FullPath like t1.FullPath + '%'

and t2.FullPath!=t1.FullPath

select

  case when rtrim(substring(FullPath, 1,20)) = Name or len(rtrim(substring(FullPath, 1,20))) = 0 then NULL else rtrim(substring(FullPath, 1,20)) end L01

, case when rtrim(substring(FullPath,21,20)) = Name or len(rtrim(substring(FullPath,21,20))) = 0 then NULL else rtrim(substring(FullPath,21,20)) end L02

, case when rtrim(substring(FullPath,41,20)) = Name or len(rtrim(substring(FullPath,41,20))) = 0 then NULL else rtrim(substring(FullPath,41,20)) end L03

, case when rtrim(substring(FullPath,61,20)) = Name or len(rtrim(substring(FullPath,61,20))) = 0 then NULL else rtrim(substring(FullPath,61,20)) end L04

, Name

from (

                                select

                                FullPath

                                ,right(FullPath,20) Name

                                from #FullPath

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

drop table #FullPath;