SQL Server – How to Convert Hierarchy into an Ordered List of Rows

hierarchysql serversql-server-2012t-sql

I tried to make a simple list that represents hierarchy of items.

Tags have simple structure like
   [Id]
  ,[Name]
  ,[ParentTagId]

I try to make the follwing result table:
ID, ParentID, Name, Lev, LevelDynamic, RowNumber
LevelDyanmic is for rendering. It contains a delta of level changes. The order of returned rows is important.

WITH cte (ParentID, ID, Name, lev, shift)
              AS(SELECT   ParentTagID,
                            ID,
                            Name,
                            1,
                            ID * 10000
                   FROM     dbo.Tag
                   where ParentTagID is null
                   UNION ALL
                   SELECT   e.ParentTagId,
                            e.ID,
                            e.Name,
                            lev + 1,
                            shift + lev * 100 + 1
                   FROM     cte
                            INNER JOIN dbo.Tag AS e ON e.ParentTagId = cte.ID
                 )
                  select cte.ID,
                     cte.ParentID,
                  cte.Name,
                  cte.lev,
                  cte.shift,
                  CAST(cte.lev - (LAG(cte.lev, 1, 0) OVER(ORDER BY cte.shift)) as int) as LevelDynamic,
                  CAST(ROW_NUMBER() OVER(ORDER BY cte.shift) as int) AS RowNumber
                  from CTE
                 order by shift asc

But that query returns me not correct results, the order of sibling elements is incorrect, it related to incorrect shift calculation. Could you help me with it ?

Best Answer

Probably you need tree nodes in depth-first order

with tag as (
    select * from (values
     (1,'.Net',cast(null as int)),
     (2,'EF',1),
     (3,'NHib',1),
     (4,'CF',2),
     (5,'Java',null),
     (6,'JRE',3)) t([Id],[Name],[ParentTagId])
),
cte (ParentID, ID, Name, lev, shift)AS(
  SELECT   ParentTagID,
                ID,
                Name,
                1,
                row_number() over(order by id)
       FROM     Tag
       where ParentTagID is null
       UNION ALL
       SELECT   e.ParentTagId,
                e.ID,
                e.Name,
                lev + 1,
                shift * 100 + row_number() over(order by e.id)
       FROM     cte
       INNER JOIN Tag AS e ON e.ParentTagId = cte.ID
)
select *,
   CAST(cte.lev - LAG(cte.lev, 1, 0) OVER(ORDER BY cast(shift as varchar(50))) as int) as LevelDynamic,
   CAST(ROW_NUMBER() OVER(ORDER BY cast(shift as varchar(50))) as int) AS RowNumber   
from cte
order by cast(shift as varchar(50))
Related Question