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

/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