CODEWITHSUNDEEP
sqlsql-servert-sql
William Maxwell
William Maxwell

Reputation: 11

Using a SQL Server table, I need to create a new table recursive?

I have a simple table of related items, like so (SQL Server db)

id   Item   Parent
1     2      5
2     4      5
3     5      12
4     6      2
5     10     6

I'd like to output a table that shows, for each Item a full path of all inter-related items (up to 4 "levels"), like so

id  Item  ParentL1  ParentL2 ParentL3 ParentL4
1    2      5         12
2    4      5         12
3    5      12
4    6      2         5         12
5    10     6         2          5     12

Thanks!

Upvotes: 1

Views: 69

Answers (3)

Lamak
Lamak

Reputation: 70668

Ok, even though the LEFT JOINs are the simplest way in this case (when only 4 levels of recursion are needed), this is another option using recursive CTEs (SQL Server 2005+):

;WITH CTE AS 
(
    SELECT *, 1 RecursionLevel
    FROM YourTable
    UNION ALL
    SELECT B.id, A.Item, B.Parent, RecursionLevel + 1
    FROM CTE A
    INNER JOIN YourTable B
        ON A.Parent = B.Item
)
SELECT  Item,
        MIN(CASE WHEN RecursionLevel = 1 THEN Parent END) ParentL1,
        MIN(CASE WHEN RecursionLevel = 2 THEN Parent END) ParentL2,
        MIN(CASE WHEN RecursionLevel = 3 THEN Parent END) ParentL3,
        MIN(CASE WHEN RecursionLevel = 4 THEN Parent END) ParentL4
FROM CTE
WHERE RecursionLevel <= 4
GROUP BY Item

This is the result:

╔══════╦══════════╦══════════╦══════════╦══════════╗
║ Item ║ ParentL1 ║ ParentL2 ║ ParentL3 ║ ParentL4 ║
╠══════╬══════════╬══════════╬══════════╬══════════╣
║    2 ║        5 ║ 12       ║ NULL     ║ NULL     ║
║    4 ║        5 ║ 12       ║ NULL     ║ NULL     ║
║    5 ║       12 ║ NULL     ║ NULL     ║ NULL     ║
║    6 ║        2 ║ 5        ║ 12       ║ NULL     ║
║   10 ║        6 ║ 2        ║ 5        ║ 12       ║
╚══════╩══════════╩══════════╩══════════╩══════════╝

And here is a sqlfiddle with a demo of this.

Upvotes: 1

Jason
Jason

Reputation: 3960

The follwoing query should do the trick

SELECT t1.id, t1.Item, t1.Parent [ParentL1], t2.Parent [ParentL2], t3.Parent [ParentL3], t4.Parent [ParentL4]
FROM MyTable t1
LEFT JOIN MyTable t2
    ON t1.Parent = t2.Item
    LEFT JOIN MyTable t3
        ON t2.Parent = t3.Item
        LEFT JOIN MyTable t4
            ON t3.Parent = t4.Item

Used the following to create the test table, MyTable to confirm the resultset

CREATE TABLE MyTable
(
id      Int IDENTITY,
Item    Int,
Parent  Int
)

INSERT MyTable
VALUES (2, 5),
        (4, 5),
        (5, 12),
        (6, 2),
        (10, 6)

Upvotes: 1

Tim Rogers
Tim Rogers

Reputation: 21713

This is the simple approach.

SELECT id, t1.Item as Item, 
   t1.Parent as ParentL1,  
   t2.Parent as ParentL2, 
   t3.Parent as ParentL3, 
   t4.Parent as ParentL4
FROM Items t1
LEFT JOIN Items t2 ON t1.Parent = t2.Id
LEFT JOIN Items t3 ON t2.Parent = t3.Id
LEFT JOIN Items t4 ON t3.Parent = t4.Id

Upvotes: 1

Related Questions