Demo

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow en español

Preguntas y respuestas para programadores y profesionales de la informática

DECLARE @Menu TABLE(
	id		 INT PRIMARY KEY,
	title	 NVARCHAR(50) NOT NULL,
	parentID INT NOT NULL,
	[order]	 TINYINT NOT NULL
);
INSERT INTO @Menu (id,title,parentID,[order]) 
SELECT 11,'Home',		0,1 UNION ALL
SELECT 12,'Settings',	0,2 UNION ALL
SELECT 13,'Change Password',12,4 UNION ALL
SELECT 14,'Update Profile',12,5 UNION ALL
SELECT 15,'Search',		0,3 UNION ALL
SELECT 16,'Options',	15,6;

WITH RecursiveCTE
AS(
	SELECT	crt.id, crt.title, crt.parentID, crt.[order],
			-- 1 AS menu_level,
			CONVERT(VARCHAR(8000), '/' + CONVERT(VARCHAR(11),crt.[order]) + '/') AS overall_order
	FROM	@Menu crt
	WHERE	crt.parentID = 0
	UNION ALL
	SELECT	cld.id, cld.title, cld.parentID, cld.[order],
			-- prt.menu_level + 1 AS menu_level,
			prt.overall_order + CONVERT(VARCHAR(11),cld.[order]) + '/'
	FROM	@Menu cld -- cld = Child
	INNER JOIN RecursiveCTE prt ON cld.parentID = prt.id -- prt = Parent
)
SELECT	*
		-- ,CONVERT(HIERARCHYID, overall_order) AS hid_overall_order -- SQL2008+ You could convert to HIERARCHYID
FROM	RecursiveCTE
ORDER BY overall_order;
--ORDER BY hid_overall_order;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results