CTE Recursion to get tree hierarchy

by

Try this:

;WITH items AS (
    SELECT EstimateItemID, ItemType
    , 0 AS Level
    , CAST(EstimateItemID AS VARCHAR(255)) AS Path
    FROM EstimateItem 
    WHERE ParentEstimateItemID IS NULL AND EstimateID = @EstimateID

    UNION ALL

    SELECT i.EstimateItemID, i.ItemType
    , Level + 1
    , CAST(Path + '.' + CAST(i.EstimateItemID AS VARCHAR(255)) AS VARCHAR(255))
    FROM EstimateItem i
    INNER JOIN items itms ON itms.EstimateItemID = i.ParentEstimateItemID
)

SELECT * FROM items ORDER BY Path

With Path – rows a sorted by parents nodes

If you want sort childnodes by ItemType for each level, than you can play with Level and SUBSTRING of Pathcolumn….

Here SQLFiddle with sample of data

More Related Contents:

  1. SQL Server CTE and recursion example
  2. Which are more performant, CTE or temporary tables?
  3. Infinite loop CTE with OPTION (maxrecursion 0)
  4. CTE to get all children (descendants) of a parent
  5. Recursive sum in tree structure
  6. SQL Recursive CTE: Finding objects linked by property
  7. How to set the maxrecursion option for a CTE inside a Table-Valued-Function
  8. Where clause not working in sql server
  9. Alias name is same as column name [closed]
  10. LocalDB deployment on client PC
  11. Understanding PIVOT function in T-SQL
  12. The order of a SQL Select statement without Order By clause
  13. Using RegEx in SQL Server
  14. What is the best way to auto-generate INSERT statements for a SQL Server table?
  15. Get size of all tables in database
  16. Fuzzy matching using T-SQL
  17. How to get first and last day of previous month (with timestamp) in SQL Server
  18. SQL Server – inner join when updating [duplicate]
  19. How do I use the CONCAT function in SQL Server 2008 R2?
  20. EXEC sp_executesql with multiple parameters
  21. newid() inside sql server function
  22. How can I use a single mssql connection pool across several routes in an Express 4 web application?
  23. When must we use NVARCHAR/NCHAR instead of VARCHAR/CHAR in SQL Server?
  24. SSIS reading LF as terminator when its set as CRLF
  25. What are some ways of accessing Microsoft SQL Server from Linux?
  26. LPAD in SQL Server 2008
  27. Generate table relationship diagram from existing schema (SQL Server) [closed]
  28. Bootstrapping SQL Express from WiX?
  29. Error converting data types when importing from Excel to SQL Server 2008
  30. CHECKSUM and CHECKSUM_AGG: What’s the algorithm?

Leave a Comment