I am dealing with some hierarchical data in the following form:
Level Parent PrimaryKey LevelDepth RevenuePct
Total NULL 2786f8161 0 100
US Total 33f254b0f 1 60
UK Total 462adbba 1 25
Asia Total 5322678b3 1 15
Mobile US 75b72bdf1 2 10
Laptop US 813784df5 2 10
PC US 9550f97c 2 15
Consulting US a44ae3ef8 2 25
Mobile UK ace663d07 2 10
Laptop UK b373e61c 2 8
PC UK ca590ef44 2 7
Mobile Asia d136f267e 2 15
and I want it to be displayed in following form:
Breakup Revenue [%]
Total 100
US 60
Mobile 10
Laptop 10
PC 15
Consulting 25
UK 25
Mobile 10
Laptop 8
PC 7
Asia 15
Mobile 15
The actual problem has 6-7 level of nesting.
I am relatively new to the field and am trying to use CTE but am having issue with join condition since the child entries repeat in different parents (i.e. I have mobile category for US, UK etc...).
Here is one way to do that. Path column is used for sorting - you should probably instead of \ concatenate fixed-width level to produce path. Query works by recursively calling cte part until no row satisfies join condition between first part of cte (expressed as cte in second part after union all) and table1.
; with cte as (
select level, parent, revenuepct, leveldepth, cast (level as varchar(1000)) Path
from table1
where parent is null
union all
select a.level, a.parent, a.revenuepct, a.leveldepth, cast (path + '\' + a.level as varchar(1000))
from table1 a
inner join cte
on a.parent = cte.level
)
-- Simple indentation
select space(leveldepth * 4) + level as Breakup,
revenuepct as [revenue %]
from cte
order by path
-- Max recursive calls, 0 = unlimited
option (maxrecursion 10)