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
That was really good piece of code and it helped me a lot in solving my issue.
Thanks Kalman,
If you permit, I want to add your link to See Also section.
See similar tree traverse logic in AdventureWorks orgchart:
www.sqlusa.com/.../organizationtree
Thanks!
Good job, overall. Curious, what happened to "101" and "108" in your data points? You have all the other values. It doesn't really matter, since it's a valid example/solution, as-is, but it just made me curious. Thanks.
TNJMAN edited Revision 25. Comment: more minor fixes