Parent - Child sql query

Question

id  parent_id
1   0
2   0
3   2
4   0
5   1
6   0

I need a query that will return parent rows (parent_id=0) followed by its child rows:

1. first parent
2. all children of first parent
3. second parent
4. all children of second parent
5. third parent
6. fourth parent

Expected result: ordered by id

id   parent_id
-------------------------------------------
1    0 (first parent)
5    1     (all children of first parent)
2    0 second parent
3    2     (all children of second parent)
4    0 third parent
6    0 fourth parent

I can use union of parents followed by all childs But that gives me parents first then the children. I need parent and immediately its children.

Anyone can help?

Answer 1

This can be accomplished using two temp tables and three variables.

CREATE TABLE #Parents
(
RowId bigint identity(1,1),
Id    bigint
)

CREATE TABLE #Results
(
RowId bigint identity(1,1),
Id bigint,
ParentId bigint
)
DECLARE @Count1 bigint
DECLARE @Count2 bigint
DECLARE @ParentId bigint
INSERT INTO #Parents
SELECT Id
FROM MyTable
WHERE ParentId = 0
ORDER BY Id
SET @Count1 = 0
SELECT @Count2 = MAX(RowId) FROM #Parents
WHILE @Count1 < @Count2
BEGIN
SET @Count1 = @Count1 +1
SELECT @ParentId = Id FROM #Parents WHERE RowId = @Count1
INSERT INTO #Results (Id, ParentId) VALUES (@Count1, 0)
INSERT INTO #Results (Id, ParentId)
SELECT ID, ParentId
FROM MyTable
WHERE ID = @Count1
ORDER BY Id
END
SELECT
Id,
ParentId
FROM #Results
ORDER BY RowId
DROP TABLE #Results
DROP TABLE #Parents