Find Parent Nodes Of Child

GetParentNodes.sql

DECLARE @ID int
SET @ID = -- ID of child node

;WITH cte AS(
	SELECT ParentId AS pid, 1 AS lvl
	FROM Categories
	WHERE Id = @ID
	UNION ALL
	SELECT a.ParentId as pid, lvl+1
	FROM Categories a
		INNER JOIN cte b ON a.Id = b.pid AND a.Id <> a.ParentId
)
SELECT pid
FROM cte
ORDER BY lvl DESC