question

SSGC avatar image
SSGC asked

Parent-Child results sort

Could you some expert help me to sort this results? The results like this: 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 ) SELECT * FROM #AA ORDER BY 1 DROP TABLE #AA ID RN PID 0 Parent NULL 1 Child 6 2 Parent NULL 3 Child 2 4 Child 2 5 Child 7 6 Parent NULL 7 Parent NULL 8 Child 2 Client want output as below: ![alt text][1] If ID is parent, the PID will be NULL. If ID is child, the PID will be related to Parent ID. Sort by ID if no child, otherwise sort by child ID. Thanks [1]: /storage/temp/3880-untitled.png
t-sqlsortingorder
untitled.png (5.9 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you very much!
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you very much!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.