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

MCITP: DBA SQL Server

---

CREATE view [dbo].[vwChecklistItems]
as

With Hierarchy(ChecklistID, ChecklistItemName, ChecklistItemID, Level, FullyQualifiedKey, FullyQualifiedName, ChecklistLevel)
As
(
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