Hirarchy (Parent -child- child)

I am trying this query every time. but I can’t get my proper output. then I continuous try this query I get this result. This query very useful me.

2016-12-29_211943.png

WITH CTE AS
(
SELECT CP.Position,CP.Description,CP.sdfParentID,ROW_NUMBER() OVER (ORDER BY POSITION) RN,CAST(‘ ‘ AS VARCHAR(8000)) AS COL
FROM sdfComPriv CP WHERE sdfParentID IS NULL
UNION ALL
SELECT CP.Position,CP.Description,CP.sdfParentID,RN,COL+CAST(RN AS VARCHAR(2))+CAST(ROW_NUMBER() OVER (ORDER BY C.Position) AS VARCHAR(10))–+CAST(LEVEL AS VARCHAR(10))
FROM sdfComPriv CP
INNER JOIN CTE C ON C.Position=CP.sdfParentID
)

SELECT * FROM CTE
ORDER BY RN,col

 

Advertisements

Author: tdmsql

I am a PL-Sql Developer. I am searching some sites sql studies but its bored. i like create studies note my own way. so that i created this site.

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