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.

 

 

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

 

2016-12-29_211943.png

 

 

 

Advertisements