This is the fourth article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:

Introduction to Common Table Expression (a.k.a CTE)
Recursive CTE
Multiple CTEs in a Single Query

Nested Common Table Expressions

Nested CTEs is a scenario where one CTE references another CTE in it.

EXAMPLE 1: Below is a basic example of a Nested CTE:

WITH FirstCTE 
   AS (SELECT 1 EmployeeId, 'Shreeganesh Biradar' Name)
, SecondCTE 
   AS (SELECT EmployeeId, Name, 'India' Country FROM FirstCTE)
SELECT *   FROM SecondCTE

RESULT:

EXAMPLE 2: Below is another example of a Nested CTE:

This example uses the Employees table created in the previous article Introduction to Common Table Expression (a.k.a CTE). You can visit the link to create the table, if you have not created it already.

WITH FirstCTE AS 
   (SELECT Id EmployeeId, Name, ManagerId 
	FROM dbo.Employees WITH(NOLOCK) 
	WHERE ManagerId is NULL)
,  	SecondCTE AS
	(SELECT E.Id EmployeeId, E.Name, E.ManagerId, 
	        FCTE.Name ManagerName
	 FROM dbo.Employees E WITH(NOLOCK)
		INNER JOIN FirstCTE FCTE
		 ON E.ManagerId = FCTE.EmployeeId)
SELECT * FROM SecondCTE

RESULT: