CODEWITHSUNDEEP
sql-server-2008
Wytas
Wytas

Reputation: 193

Hierarchy table query

I have a hierarchy table which looks like this:

id  child_id
1   2
2   3
2   4
3   5
3   6
3   7
4   8
4   9
4   10

How to write a query to get results in such format:

1
|_ 2
   |_ 3
   |  |_ 5
   |  |_ 6
   |  |_ 7
   |_ 4
      |_ 8
      |_ 9
      |_ 10

Upvotes: 0

Views: 86

Answers (1)

Joao Leal
Joao Leal

Reputation: 5542

This is the best I could do:

WITH cte AS (
SELECT id, CAST(NULL AS INT) as Parent, 1 AS Level, CAST(id AS varchar(255)) AS LevelChar FROM Hier WHERE id = 1
UNION ALL
SELECT child_id AS id, hier.id AS Parent, Level + 1, CAST(LevelChar + '-' + CAST(child_id AS varchar(255)) AS varchar(255)) FROM Hier 
JOIN cte ON Hier.id = cte.id
)
SELECT ISNULL(REPLICATE(Space(2) + '|',Level-1),'') + CAST(id AS Varchar(255)) FROM cte
ORDER BY LevelChar, ID

Upvotes: 1

Related Questions