CTE - Common Table Expressions

A common table expression (CTE) is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

Using Common Table Expression to traverse the adjacency List (parent / child) Model.

WITH StructureLevels
AS
(
	-- base
	SELECT
		StructureId,
		ParentStructureId,
		SortOrder,
		(CONVERT(VARCHAR(MAX)
         , REPLICATE ('0', 6-Len(CAST(SortOrder as Varchar)))) + CONVERT(VARCHAR(MAX), SortOrder))
          AS thePath,
		1 AS Level
	FROM DocumentStructure
	WHERE
	StructureId = @RootStructureId

	UNION ALL
	-- recursive
	SELECT
	DS.StructureId,
	DS.ParentStructureId,
	DS.SortOrder,
	SL.thePath + '.' 
       + CONVERT(VARCHAR(MAX)
         , REPLICATE ('0', 6-Len(Cast(DS.SortOrder as Varchar)))) + CONVERT(VARCHAR(MAX), DS.SortOrder)
       AS thePath,
	SL.Level + 1 AS Level
	FROM StructureLevels SL
	JOIN DocumentStructure DS
    ON DS.ParentStructureId = SL.StructureId
),
StructureRows AS
(
	SELECT
		 StructureLevels.*,
		 ROW_NUMBER() OVER (ORDER BY thePath) AS Row
	FROM StructureLevels
)
    
SELECT ER.StructureId,
       ER.ParentStructureId,
	   ER.SortOrder,
	   ER.thePath,
       ER.Level NodeLevel,
       (ER.Row * 2) - ER.Level AS NodeLeft,
       ((ER.Row * 2) - ER.Level) + 
			(
				SELECT COUNT(*) * 2
				FROM StructureRows ER2 
				WHERE ER2.thePath LIKE ER.thePath + '.%'
			) + 1 AS NodeRight
FROM StructureRows ER