hunchback (9/22/2013)

---

One way to accomplish this is using a varbinary column to concatenate the sequence of numbers based on the position value.

WITH C1 AS (

SELECT

Id, IdRoot, Name, Position,

CAST(ROW_NUMBER() OVER(ORDER BY position) AS varbinary(900)) AS order_val,

0 AS lvl

FROM

menu

WHERE

IdRoot = 0

UNION ALL

SELECT

C.Id, C.IdRoot, C.Name, C.Position,

CAST(P.[path] + CAST(ROW_NUMBER() OVER(PARTITION BY P.IdRoot ORDER BY C.Position) AS BINARY(8)) AS varbinary(900)),

P.lvl + 1

FROM

C1 AS p

INNER JOIN

menu AS C

ON p.Id = C.IdRoot

)

SELECT REPLICATE(SPACE(4), 2 * lvl) + [name] AS menu

FROM C1

ORDER BY order_val;

GO

/*

menu

ERP

ACC

HR

PAYMENT

PROCESS

CANCEL

VACATIONS

ABSENTS

SALES

*/

To generate the numbers in specific order we can use a ranking function like ROW_NUMBER, where you can use multiple columns to drive the sort order (by name or position or whatever other combination).

You can read more about other uses of he ranking functions in the last book from Itzik Ben-Gan about T-SQL Querying.

Inside Microsoft® SQL Server® 2008: T-SQL Querying

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions