Archiv štítku: SQL

rCTE

Jednoduchý příklad

WITH Projekt (id, name, parent) AS 
(
	          SELECT 1, 'A verze 2',  4
	UNION ALL SELECT 2, 'A verze 0',  NULL
	UNION ALL SELECT 3, 'B verze 2a', 8
	UNION ALL SELECT 4, 'A verze 1',  2
	UNION ALL SELECT 5, 'B verze 3b', 7
	UNION ALL SELECT 6, 'B verze 1b', 9
	UNION ALL SELECT 7, 'B verze 2b', 6
	UNION ALL SELECT 8, 'B verze 1a', 9
	UNION ALL SELECT 9, 'B verze 0',  NULL
),
Hierarchy AS
(
	SELECT id, name, parent, [level]=1, projekt=ROW_NUMBER() OVER (ORDER BY name)
	FROM Projekt
	WHERE parent IS NULL

	UNION ALL

	SELECT p.id, p.name, p.parent, h.[level]+1, projekt
	FROM Projekt p
	JOIN Hierarchy h ON p.parent=h.id
)
SELECT *
FROM Hierarchy
ORDER BY projekt, [level]