Generate a Complete List of Parent-Child IDs in Single Column Using SQL

I have seen numerous examples on this, but none do exactly what I need…and I am having a hard time figuring it out. (all the examples I see list the results in 2 columns…I don’t need that)

I have a table named ‘Documents’ with 2 columns

• Id
• ParentId (nullable)

Like so…

CREATE TABLE [dbo].[Document](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL
)

Populated as such…

INSERT INTO [dbo].[Document] SELECT 1, NULL;
INSERT INTO [dbo].[Document] SELECT 2, 1;
INSERT INTO [dbo].[Document] SELECT 3, 2;
INSERT INTO [dbo].[Document] SELECT 4, NULL;
INSERT INTO [dbo].[Document] SELECT 5, 4;
INSERT INTO [dbo].[Document] SELECT 6, NULL;

I need to recursively traverse the parent-child relationship (until NULL) & get a list of all the Id’s into a single column, like so…

Answer

(I assume based on syntax that you are using SQL Server.)

You can use a recursive CTE:

with cte as (
      select id, parentid, 1 as lev
      from document d
      where id = 3
      union all
      select d.id, d.parentid, lev + 1
      from cte join
           document d
           on cte.parentid = d.id
     )
select id
from cte
order by lev;

Here is a SQL Fiddle.

Note: You cannot insert (normally) into a column declared as identity. If you want to put in the values, remove the identity() attribute.