Sql query for tree table

by

Expanding on a_horse_with_no_name’s answer, this show how to use SQL Server’s implementation of recursive CTE (recursive single-record cross apply) in combination with row_number() to produce the exact output in the question.

declare @t table(id int,parentId int,name varchar(20))
insert @t select 1,  0        ,'Category1'
insert @t select 2,  0,        'Category2'
insert @t select 3,  1,        'Category3'
insert @t select 4 , 2,        'Category4'
insert @t select 5 , 1,        'Category5'
insert @t select 6 , 2,        'Category6'
insert @t select 7 , 3,        'Category7'
;

WITH tree (id, parentid, level, name, rn) as 
(
   SELECT id, parentid, 0 as level, name,
       convert(varchar(max),right(row_number() over (order by id),10)) rn
   FROM @t
   WHERE parentid = 0

   UNION ALL

   SELECT c2.id, c2.parentid, tree.level + 1, c2.name,
       rn + "https://stackoverflow.com/" + convert(varchar(max),right(row_number() over (order by tree.id),10))
   FROM @t c2 
     INNER JOIN tree ON tree.id = c2.parentid
)
SELECT *
FROM tree
ORDER BY cast("https://stackoverflow.com/" + RN + "https://stackoverflow.com/" as hierarchyid)

To be honest, using the IDs themselves to produce the tree “path” would work, since we are ordering directly by id, but I thought I’d slip in the row_number() function.

More Related Contents:

  1. Efficiently convert rows to columns in sql server
  2. T-SQL split string
  3. How can I get column names from a table in SQL Server?
  4. Conditional WHERE clause in SQL Server
  5. How can I select the first day of a month in SQL?
  6. Multi-statement Table Valued Function vs Inline Table Valued Function
  7. SQL Server Insert if not exists
  8. GROUP BY to combine/concat a column [duplicate]
  9. Select statement to find duplicates on certain fields
  10. Copy data into another table
  11. Using a Variable in OPENROWSET Query
  12. How to find current transaction level?
  13. How do I see active SQL Server connections?
  14. EXISTS vs JOIN and use of EXISTS clause
  15. Check for file exists or not in sql server?
  16. UPDATE if exists else INSERT in SQL Server 2008 [duplicate]
  17. Group DateTime into 5,15,30 and 60 minute intervals
  18. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  19. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  20. Alternate of lead lag function in SQL Server 2008
  21. UNIX_TIMESTAMP in SQL Server
  22. Which SQL query is faster? Filter on Join criteria or Where clause?
  23. UPDATE and REPLACE part of a string
  24. Generate random int value from 3 to 6
  25. Improve SQL Server query performance on large tables
  26. Add emoji / emoticon to SQL Server table
  27. Select SQL Server database size
  28. How to execute a stored procedure inside a select query
  29. Cannot Resolve Collation Conflict
  30. Display next event date

Leave a Comment