SQL Server recursive self join

by

Recursive cte to the rescue….

Create and populate sample table (Please save us this step in your future questions):

DECLARE @T as table
(
    id int,
    name varchar(100),
    parent_id int
)

INSERT INTO @T VALUES
(1, 'A', NULL),
(2, 'A.1', 1),
(3, 'A.2', 1),
(4, 'A.1.1', 2),
(5, 'B', NULL),
(6, 'B.1', 5),
(7, 'B.1.1', 6),
(8, 'B.2', 5),
(9, 'A.1.1.1', 4),
(10, 'A.1.1.2', 4)

The cte:

;WITH CTE AS
(
    SELECT id, name, name as path, parent_id
    FROM @T 
    WHERE parent_id IS NULL
    UNION ALL
    SELECT t.id, t.name, cast(cte.path +','+ t.name as varchar(100)), t.parent_id
    FROM @T t
    INNER JOIN CTE ON t.parent_id = CTE.id
)

The query:

SELECT id, name, path
FROM CTE

Results:

id      name        path
1       A           A
5       B           B
6       B.1         B,B.1
8       B.2         B,B.2
7       B.1.1       B,B.1,B.1.1
2       A.1         A,A.1
3       A.2         A,A.2
4       A.1.1       A,A.1,A.1.1
9       A.1.1.1     A,A.1,A.1.1,A.1.1.1
10      A.1.1.2     A,A.1,A.1.1,A.1.1.2

See online demo on rextester

More Related Contents:

  1. Simplest way to do a recursive self-join?
  2. Is it possible to use greater than equal to operator in group by? [closed]
  3. Identity increment is jumping in SQL Server database
  4. How can I list all foreign keys referencing a given table in SQL Server?
  5. Select n random rows from SQL Server table
  6. What is the benefit of using “SET XACT_ABORT ON” in a stored procedure?
  7. How to implement LIMIT with SQL Server? [duplicate]
  8. converting Epoch timestamp to sql server(human readable format)
  9. What are Covering Indexes and Covered Queries in SQL Server?
  10. How do I split a delimited string so I can access individual items?
  11. What’s the best way to select the minimum value from several columns?
  12. Add a summary row with totals
  13. Counting DISTINCT over multiple columns
  14. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  15. Why use Select Top 100 Percent?
  16. How to check if a stored procedure exists before creating it
  17. How do I find duplicates across multiple columns?
  18. Calculate business hours between two dates
  19. Insert Into… Merge… Select (SQL Server)
  20. Correct use of transactions in SQL Server
  21. SSIS Source Format Implicit Conversion for Datetime
  22. store arabic in SQL database
  23. What is the Null Character literal in TSQL?
  24. UNION the results of multiple stored procedures
  25. how does SELECT TOP works when no order by is specified?
  26. Script entire database SQL-Server
  27. Why is 30 the default length for VARCHAR when using CAST?
  28. SQL can I have a “conditionally unique” constraint on a table?
  29. Count consecutive duplicate values in SQL
  30. How to improve performance for datetime filtering in SQL Server?

Leave a Comment