Selecting the Parent Description in a Hierarchy using a CTE

Some sample code for how to get the parent’s description in a parent child hierarchy using a CTE.

CREATE TABLE dbo.PCH

( id int null, description varchar(10) null, parentid int null )

INSERT PCH VALUES (5, 'Id Four', 4)

INSERT PCH VALUES (4, 'Id Four', 2)

INSERT PCH VALUES (3, 'Id Three', 2)

INSERT PCH VALUES (2, 'Id Two', 1)

INSERT PCH VALUES (1, 'Id One', null)

/* Get the Parent Description*/
;WITH Hierarchy1 (id, description, level, parentid, parentdescription)
AS (SELECT T1.id, T1.description, 0, T1.parentid, convert(varchar(10),'') AS ParentDescription
FROM PCH T1
WHERE T1.parentid IS NULL
UNION ALL
SELECT T1.id, T1.description, TH.Level+1, T1.parentid, TH.description AS ParentDescription
FROM PCH T1 INNER JOIN Hierarchy1 TH ON TH.id = T1.Parentid )
SELECT * FROM Hierarchy1

/*Get the very Top Parent*/
;WITH Hierarchy1 (id, level,parentid, topparent)
AS
(
 SELECT
 T1.id, 0,T1.parentid, T1.id as topparent
 FROM
 PCH T1

 WHERE
 T1.parentid IS NULL

 UNION ALL
 SELECT
 T1.id, TH.Level+1, T1.parentid,
 CASE WHEN TH.parentid IS NULL THEN T1.parentid ELSE TH.parentid END as topparent
 FROM
 PCH T1
 INNER JOIN Hierarchy1 TH ON TH.id = T1.Parentid

)

/* Get Hierarchy Results*/
SELECT * FROM Hierarchy1

/*Has Children*/

SELECT Hierarchy1.Id, Hierarchy1.Level, CASE WHEN EXISTS
 (SELECT TOP 1 1 --you can actually select anything you want here
 FROM Hierarchy1 Hierarchy1Check
 WHERE Hierarchy1Check.ParentId = Hierarchy1.Id
 ) THEN 1 ELSE 0 END AS HasRows
FROM Hierarchy1


About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s