Simplest way to do a recursive self-join?
Using CTEs you can do it this way
DECLARE @Table TABLE(
PersonID INT,
Initials VARCHAR(20),
ParentID INT
)
INSERT INTO @Table SELECT 1,'CJ',NULL
INSERT INTO @Table SELECT 2,'EB',1
INSERT INTO @Table SELECT 3,'MB',1
INSERT INTO @Table SELECT 4,'SW',2
INSERT INTO @Table SELECT 5,'YT',NULL
INSERT INTO @Table SELECT 6,'IS',5
DECLARE @PersonID INT
SELECT @PersonID = 1
;WITH Selects AS (
SELECT *
FROM @Table
WHERE PersonID = @PersonID
UNION ALL
SELECT t.*
FROM @Table t INNER JOIN
Selects s ON t.ParentID = s.PersonID
)
SELECT *
FROm Selects
WITH q AS
(
SELECT *
FROM mytable
WHERE ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
UNION ALL
SELECT m.*
FROM mytable m
JOIN q
ON m.parentID = q.PersonID
)
SELECT *
FROM q
By adding the ordering condition, you can preserve the tree order:
WITH q AS
(
SELECT m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
FROM mytable m
WHERE ParentID IS NULL
UNION ALL
SELECT m.*, q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
FROM mytable m
JOIN q
ON m.parentID = q.PersonID
)
SELECT *
FROM q
ORDER BY
bc
By changing the ORDER BY
condition you can change the ordering of the siblings.
The Quassnoi query with a change for large table. Parents with more childs then 10: Formating as str(5) the row_number()
WITH q AS
(
SELECT m.*, CAST(str(ROW_NUMBER() OVER (ORDER BY m.ordernum),5) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
FROM #t m
WHERE ParentID =0
UNION ALL
SELECT m.*, q.bc + '.' + str(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.ordernum),5) COLLATE Latin1_General_BIN
FROM #t m
JOIN q
ON m.parentID = q.DBID
)
SELECT *
FROM q
ORDER BY
bc