Cacher is the code snippet organizer for pro developers

DECLARE @nodeId uniqueidentifier
SELECT @nodeId = 0 -- Put your node Id here

	WITH NodeTree(ID, ParentID, [Level])
	AS
	(
		-- The immediate Node we're getting (which will be excluded in the final query)
		SELECT a.ID, a.ParentID, 0
			FROM Node a
			WHERE ID = @nodeId 

		UNION ALL

		-- Recursively retrieve the children
		SELECT a.ID, a.ParentID, [Level] + 1
			FROM Node a 
				INNER JOIN NodeTree p ON a.ParentID = p.ID
	)

	SELECT a.* 
		FROM [Node] a 
			INNER JOIN NodeTree tree ON a.ID = tree.ID 
			
		WHERE 
			a.ID <> @nodeId -- Delete the where clause to include the node within the results
			
		ORDER BY tree.[Level], a.sortOrder, a.[text]