IF OBJECT_ID(
'tempdb..#test'
,
'U'
)
IS
NOT
NULL
DROP
TABLE
#test;
GO
CREATE
#test
(
Childid
INT
parentid
);
INSERT
INTO
( Childid, parentid )
VALUES
( 100, 0 ),
( 102, 100 ),
( 103, 100 ),
( 104, 102 ),
( 105, 102 ),
( 106, 104 ),
( 107, 103 ),
( 109, 105 );
DECLARE
@rootId
AS
= 100;
WITH
Subs
SELECT
Childid ,
1
lvl ,
CAST
(1
VARBINARY(
MAX
))
PathSort
FROM
WHERE
Childid = @rootId
UNION
ALL
C.Childid ,
P.lvl + 1 ,
P.PathSort +
(ROW_NUMBER() OVER ( PARTITION
BY
C.parentid
ORDER
C.Childid )
BINARY
(5))
P
JOIN
C
ON
C.parentid = P.Childid
ROW_NUMBER() OVER (
PathSort )
CustomSort,
REPLICATE(
' | '
, lvl) +
(Childid
NVARCHAR(100)) ChildInTree
CustomSort;
TNJMAN edited Revision 28. Comment: added wiki reference/citation for the definition of a "tree"
TNJMAN edited Revision 27. Comment: minor fix.
TNJMAN edited Revision 26. Comment: added acyclic digraph wiki ref/citation
TNJMAN edited Revision 24. Comment: minor syntax correction ("...desired order is shown below.")
TNJMAN edited Revision 23. Comment: Utilize pre-defined acronym vs. full name (i.e., "DAG")
TNJMAN edited Revision 22. Comment: Parenthetical
TNJMAN edited Revision 21. Comment: more bling
TNJMAN edited Revision 20. Comment: some cleanup
TNJMAN edited Revision 19. Comment: clarified the Solution section.
TNJMAN edited Revision 18. Comment: more cleanup
Very interesting problem!
Thanks so much!
Maheshkumar S Tiwari edited Revision 5. Comment: Added Tag
Maheshkumar S Tiwari edited Revision 7. Comment: Corrected Tags
Good one !!!
Thanks for your feedback.
Saeid Hasani edited Revision 8. Comment: Add See Also section.
Richard Mueller edited Revision 11. Comment: Fix headings in HTML
Richard Mueller edited Revision 12. Comment: Replace RGB values with color names in HTML to restore colors, removed tag
TNJMAN edited Revision 13. Comment: changed "comes up from" to "is derived from"
TNJMAN edited Revision 14. Comment: clarified idiomatic syntactical inconsistencies "addresses the task of..."
TNJMAN edited Revision 15. Comment: more basic edits
TNJMAN edited Revision 16. Comment: standard syntax edits
TNJMAN edited Revision 17. Comment: cleaned up more syntax issues