So fügen wir die Level und IsLeaf Information zu einer ParentChild Struktur hinzu:

With Hierarchy(AccName, AccKey, ParentAccKey, Level) As (

Select E.AccountName, E.AccountKey,E.ParentAccountKey, 1     From DimAccount E

Where E.ParentAccountKey is null

Union all

Select E.AccountName, E.AccountKey,E.ParentAccountKey, H.Level+1     from DimAccount E

inner join Hierarchy H on H.AccKey=E.ParentAccountKey )

Select H.AccName, H.AccKey, h.ParentAccKey, H.Level ,

CASE WHEN j.ParentAccountKey is null THEN 1 ELSE NULL END as IsLeaf from Hierarchy H

Left Join (Select distinct ParentAccountKey from DimAccount) J on j.ParentAccountKey = h.AccKey

Advertisements