With Breadcrumb( id, parentid, description) as
(
SELECT id, parentid, Description
FROM RadMenu
--where parentid IS NOT NULL
)
select parentid, id, description
from breadcrumb
order by parentid
Here's some of the data, filtered to show 1 example:
id parentid Description
57 NULL Adaptors
74 57 CompactFlash to IDE
75 57 CompactFlash to SATA
76 57 PCMCIA to CompactFlash
978 57 CFast to SATA
275 74 IDE to 1 CompactFlash Slot
525 74 IDE to 2 CompactFlash Slots
555 74 IDE to 1 CompactFlash Slot
ASKER
ASKER
WITH Breadcrumb AS (
SELECT id, parentid, Description
FROM RadMenu
WHERE id = @id
UNION ALL
SELECT r.id, r.parentid, r.Description
FROM RadMenu r
-- here starts the "trick"
JOIN Breadcrumb b
ON b.id = r.parentid
)
SELECT id, parentid, Description
FROM Breadcrumb
ASKER
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY