CODEWITHSUNDEEP
sqlsql-server
Prisoner ZERO
Prisoner ZERO

Reputation: 14166

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

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...

enter image description here

Upvotes: 1

Views: 2223

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

(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.

Upvotes: 4

Related Questions