Common Table Expressions for flattening Parent Child Relationships

I’m a big fan of parent-child relationships.. I really got into them originally because Analysis Services does such an AWESOME job of working with parent childs.

But now.. we frequently need to utilize Parent-Child relationships into a VIEW in order to use it in stored procedures, queries, etc

Here is an example of a common table expression that I have used in order to magically ‘flatten’ this relationship.

-Aaron Kempf


CREATE view [dbo].[vwChecklistItems]

With Hierarchy(ChecklistID, ChecklistItemName, ChecklistItemID, Level, FullyQualifiedKey, FullyQualifiedName, ChecklistLevel)
Select E.ChecklistID, E.ChecklistItemName, E.ChecklistItemID, 0, Cast(‘/’+ CONVERT(VARCHAR(50),E.ChecklistItemID) as Varchar(MAX)), Cast(‘/’+ CONVERT(VARCHAR(50),E.ChecklistItemName) as Varchar(MAX)), E.ChecklistLevel
From dbo.ChecklistItems E
Where E.ChecklistItemParent is null
Union all
Select E.ChecklistID, E.ChecklistItemName, E.ChecklistItemID, H.Level+1, H.FullyQualifiedKey+’/’+CONVERT(VARCHAR(50), E.ChecklistItemID), H.FullyQualifiedName+’/’+CONVERT(VARCHAR(50), E.ChecklistItemName), E.ChecklistLevel
from dbo.ChecklistItems E
inner join Hierarchy H on H.ChecklistItemID=E.ChecklistItemParent

Select TOP 100 PERCENT Space(Level*4) + H.ChecklistItemName as IndentedChecklistItemName, ChecklistID, ChecklistItemName, ChecklistItemID, Level, FullyQualifiedKey, FullyQualifiedName, ChecklistLevel,
Select TOP 1 ChecklistItemName
From Hierarchy SUbQ
Where REPLACE(LEFT(H.FullyQualifiedKey, 6), ‘/’, ”) = REPLACE(LEFT(SubQ.FullyQualifiedKey, 6), ‘/’, ”)

) as TopLevelParent
from Hierarchy H
order by H.FullyQualifiedKey

This results in a dataset that looks like this

FullyQualifiedKey FullyQualifiedName
/1002 /Existing Roofing Condition
/1002/1004 /Existing Roofing Condition/No Damage Observed
/1002/1007 /Existing Roofing Condition/Took Picture of Damage
/1008 /Building Stories
/1008/1009 /Building Stories/One
/1008/1010 /Building Stories/Two
/1008/1011 /Building Stories/Stories above two
/1014 /Number of Layers
/1014/1015 /Number of Layers/Single
/1014/1016 /Number of Layers/Two
/1014/1017 /Number of Layers/More Than Two
/1018 /Shingle Damage
/1018/1019 /Shingle Damage/Torn
/1018/1020 /Shingle Damage/Missing
/1018/1022 /Shingle Damage/Curling
/1018/1024 /Shingle Damage/Hail/Wind Damage
/1025 /Building Exterior
/1025/1026 /Building Exterior/No Damage Observed
/1025/1028 /Building Exterior/Took Picture of Damage
/1025/1029 /Building Exterior/Hail and Wind Hits
/1025/1030 /Building Exterior/Doors
/1025/1032 /Building Exterior/Siding
/1025/1033 /Building Exterior/Fascia
/1025/1034 /Building Exterior/Soffit
/1025/1035 /Building Exterior/Window/Window Screens
/1025/1036 /Building Exterior/Decks
/1025/1037 /Building Exterior/Fences
/1025/1038 /Building Exterior/AC Units
/1025/1039 /Building Exterior/Patios
/1025/1040 /Building Exterior/Pools/Hot Tubs
/1025/1043 /Building Exterior/Gutters