Recursive CTE View of a Tree

An example of recursive CTE SQL to query a Tree.

Apologies for SQL Server syntax.

WITH TREE (TREE_NODE, TREE_LEVEL_NUM, PARENT_NODE_NAME, PATH_TO, DEPARTMENT, GRP, DIVISION, BRANCH, SECTION, UNIT) AS (

	SELECT 
 	  TREE_NODE
	, TREE_LEVEL_NUM
	, PARENT_NODE_NAME
	, CONVERT(VARCHAR(MAX), TREE_NODE) PATH_TO 
	, TREE_NODE DEPARTMENT
	, CONVERT(VARCHAR(20), ' ') GRP
	, CONVERT(VARCHAR(20), ' ') DIVISION
	, CONVERT(VARCHAR(20), ' ') BRANCH
	, CONVERT(VARCHAR(20), ' ') SECTION
	, CONVERT(VARCHAR(20), ' ') UNIT
	FROM PSTREENODE
	WHERE TREE_NAME = 'YOUR_TREE_NAME' 
	AND EFFDT = '2021-01-01'
	AND PARENT_NODE_NAME = ' '

	UNION ALL

	SELECT 
	  C2.TREE_NODE
	, C2.TREE_LEVEL_NUM
	, C2.PARENT_NODE_NAME
	, REPLACE(TREE.PATH_TO + '/' + CONVERT(VARCHAR(MAX), C2.TREE_NODE), ' ', '') PATH_TO
	, TREE.DEPARTMENT
	, CASE WHEN C2.TREE_LEVEL_NUM = 2 THEN CONVERT(VARCHAR(20), C2.TREE_NODE) ELSE TREE.GRP END
	, CASE WHEN C2.TREE_LEVEL_NUM = 3 THEN CONVERT(VARCHAR(20), C2.TREE_NODE) ELSE TREE.DIVISION END
	, CASE WHEN C2.TREE_LEVEL_NUM = 4 THEN CONVERT(VARCHAR(20), C2.TREE_NODE) ELSE TREE.BRANCH END
	, CASE WHEN C2.TREE_LEVEL_NUM = 5 THEN CONVERT(VARCHAR(20), C2.TREE_NODE) ELSE TREE.SECTION END
	, CASE WHEN C2.TREE_LEVEL_NUM = 6 THEN CONVERT(VARCHAR(20), C2.TREE_NODE) ELSE TREE.UNIT END
	FROM PSTREENODE C2
	INNER JOIN TREE 
		ON TREE.TREE_NODE = C2.PARENT_NODE_NAME
	WHERE C2.TREE_NAME = 'YOUR_TREE_NAME' 
	AND C2.EFFDT = '2021-01-01'

)

SELECT *
FROM TREE 
WHERE TREE_NODE = 'X'
;