question

Try this: SELECT CASE WHEN RN = 'Parent' THEN ID END AS Parent , CASE WHEN RN = 'Child' THEN ID END AS Child , RN , PID FROM #AA ORDER BY CASE WHEN RN = 'Parent' THEN ID WHEN RN = 'Child' THEN PID END , RN DESC , -- to show Parent records above Child records ID ; -- to order the children You'll need to do some fiddling at the presentation level to convert NULLs into blanks in the first two columns. Either that, or change the first two columns of the select appropriately.

One way of doing this is to use a "materialized hierarchical path" - a value on each row that gives a value according to the position in the hierarchy, then you simply output in this order. CREATE TABLE #AA ( [ID] [int] NOT NULL , [RN] CHAR(10) NOT NULL , [PID] [int] NULL ) INSERT INTO #AA ( ID, RN, PID ) VALUES ( 8, 'Child', 2 ), ( 0, 'Parent', NULL ), ( 2, 'Parent', NULL ), ( 5, 'Child', 7 ), ( 4, 'Child', 2 ), ( 3, 'Child', 2 ), ( 6, 'Parent', NULL ), ( 7, 'Parent', NULL ), ( 1, 'Child', 6 ); WITH cteHierarchy AS ( SELECT cteAnchor.ID, cteAnchor.RN, cteAnchor.PID, SortPath = CAST(CAST( cteAnchor.ID AS BINARY(4)) AS VARBINARY(4000)) FROM #AA AS cteAnchor WHERE PID IS NULL UNION ALL SELECT CteRecursive.ID, CteRecursive.RN, CteRecursive.PID, SortPath = CAST(cteHierarchy.SortPath + CAST( CteRecursive.ID AS BINARY(4)) AS VARBINARY(4000)) FROM #AA AS CteRecursive INNER JOIN cteHierarchy ON cteHierarchy.ID = CteRecursive.PID ) select case when RN = 'Parent' then cast(ID as varchar) else '' end as parent, case when RN = 'Child' then cast(ID as varchar) else '' end as child, RN, PID from cteHierarchy order by cteHierarchy.SortPath DROP TABLE #AA gives the result parent child RN PID ----------- -------- ---------- ----------- 0 Parent NULL 2 Parent NULL 3 Child 2 4 Child 2 8 Child 2 6 Parent NULL 1 Child 6 7 Parent NULL 5 Child 7