Here is the code with some "real" data...
The menu can be customized by the used, including the name (it's an alias).
The table has other columns, like the original Id from our menu, but for the case it's unnecessay.
IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'menu')
DROP TABLE menu
GO
/*
Id - the menu entry Id
IdRoot - the menu entry root Id (father)
Name - description for the entry
Position - the position of the entry on it's father (1st child, 2nd child, ....)
*/
CREATE TABLE menu (Id TINYINT NOT NULL, IdRoot TINYINT NULL, Name VARCHAR(10) NOT NULL, Position TINYINT NOT NULL)
GO
INSERT INTO menu (Id, idRoot, Name, Position) VALUES
(1, 0, 'ERP', 1),
(4, 1, 'ACC', 1),
(2, 1, 'HR', 2),
(3, 2, 'PAYMENT', 1),
(6, 3, 'PROCESS', 1),
(8, 6, 'CANCEL', 1),
(5, 2, 'VACATIONS', 2),
(9, 2, 'ABSENTS', 3),
(7, 1, 'SALES', 3)
GO
WITH menuCTE (Id, IdRoot, Name, Position, Ord) AS (
SELECT Id, IdRoot, Name, Position, 0 FROM menu WHERE IdRoot = 0
UNION ALL
SELECT m.Id, m.IdRoot, m.Name, m.Position, mCTE.Ord + 1 FROM menu m INNER JOIN menuCTE mCTE ON m.IdRoot = mCTE.Id
)
SELECT * FROM menuCTE
The SELECT output is:
Id IdRoot Name Position Ord
---- ------ ---------- -------- -----------
1 0 ERP 1 0
2 1 HR 2 1
3 2 PAYMENT 1 2
4 1 ACC 1 1
5 2 VACATIONS 2 2
6 3 PROCESS 1 3
7 1 SALES 3 1
8 6 CANCEL 1 4
9 2 ABSENTS 3 2
The output desired is:
Id IdRoot Name Position Ord
---- ------ ---------- -------- -----------
1 0 ERP 1 0
4 1 ACC 1 1
2 1 HR 2 1
3 2 PAYMENT 1 2
6 3 PROCESS 1 3
8 6 CANCEL 1 4
5 2 VACATIONS 2 2
9 2 ABSENTS 3 2
7 1 SALES 3 1
Thanks,
Pedro