Parent Child CTE SQL Server

Parent_Child_CTE_SQL_Server.sql

CREATE TABLE #Organization(Id INT, ParentID INT, Name nvarchar(20));

INSERT INTO #Organization VALUES(1,NULL,'A');
INSERT INTO #Organization VALUES(2,NULL,'B');
INSERT INTO #Organization VALUES(3,NULL,'C');

INSERT INTO #Organization VALUES(4,1,'A->D');
INSERT INTO #Organization VALUES(5,2,'B->E');
INSERT INTO #Organization VALUES(6,3,'C->F');

INSERT INTO #Organization VALUES(7,4,'A->D->G');
INSERT INTO #Organization VALUES(8,5,'B->E->H');
INSERT INTO #Organization VALUES(9,6,'C->F->J');

INSERT INTO #Organization VALUES(10,7,'A->D->G->K');
INSERT INTO #Organization VALUES(11,8,'B->E->H->L');
INSERT INTO #Organization VALUES(12,9,'C->F->J->M');

DECLARE @Id int = 1;

--Get parents of @Id
WITH Parent AS (
		SELECT Id, ParentID, Name
		FROM #Organization
		WHERE Id = @Id
	UNION ALL
		SELECT a.Id, a.ParentID,a.Name
		FROM #Organization a
		INNER JOIN Parent B ON B.ID = A.ParentID
), 
	--Get Childs of @Id
Child AS (
		SELECT Id, ParentID, Name
		FROM #Organization
		WHERE Id = @Id 
	UNION ALL
		SELECT a.Id, a.ParentID, a.Name
		FROM #Organization A
		INNER JOIN Child B ON B.ParentID = A.ID
		)

	SELECT * FROM Parent
	UNION
	SELECT * FROM Child

	DROP TABLE #Organization