CODEWITHSUNDEEP
sqlsql-server
Ricardo
Ricardo

Reputation: 468

Ordering values to show in a hierarchy view

I'm trying to order a table to show it in a hierarchy view and to have less trouble with my C# code.

I've created a simplified table to show what I'm trying to achieve: (fiddle).

What I have: 

| id | parent | level |
|----|--------|-------|
|  2 | (null) |     1 |
|  7 |      2 |     2 |
|  8 |      2 |     2 |
|  9 |      7 |     3 |
| 12 |      7 |     3 |
| 14 |      9 |     4 |
| 15 |     12 |     4 |

What I need:

| id | parent | level |
|----|--------|-------|
|  2 | (null) |     1 |
|  7 |      2 |     2 |
|  9 |      7 |     3 |
| 14 |      9 |     4 |
| 12 |      7 |     3 |
| 15 |     12 |     4 |
|  8 |      2 |     2 |

So I'll be able to get this result and directly post it like the following:

ID
--02
----07
------09
--------14
------12
--------15
----08

Upvotes: 1

Views: 36

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

Something like this?

with MyCte as
(
    select id
        , parent
        , level 
        , CAST(ID as varchar(50)) as SortOrder
        from posts 
        where parent is null
    union all
    select p.id
        , p.parent
        , p.level
        , CAST(p2.SortOrder + '.' + right('000' + CAST(p.ID as varchar(10)), 3) as varchar(50))
    from posts p
    inner join MyCte p2 on p2.Id = p.parent
)

select *, 
replicate('-', level) + cast(id as varchar(10))
from MyCte
order by SortOrder

Upvotes: 2

Related Questions