Hierarchy table query

Question

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

Can you show us your atempt? or explain a logic you have tried? — Radu Gheorghiu
– Radu Gheorghiu, Commented Jun 28, 2013 at 9:52
Do all your formatting stuff on client-side, not server-side. — Andrey Gordeev
– Andrey Gordeev, Commented Jun 28, 2013 at 9:56
@RaduGheorghiu I am not in attempts phase yet as I just do not know how to approach this problem. — Wytas
– Wytas, Commented Jun 28, 2013 at 10:15

Joao Leal · Accepted Answer · 2013-06-28 14:24:39Z

1

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

answered Jun 28, 2013 at 14:24

Joao Leal

5,5521 gold badge15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hierarchy table query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related