CTE within CTE - SQL Server by serverku

As per requirement in custom logic, we need to require CTE (Common Table Expression) something like for the hierarchy, to find duplicate and remove data or for some other stuff. Recently i have used CTE within CTE for one logic and maxrecursion option as well.  So we will look at how we can use CTE inside CTE or multiple CTEs. Let us create one requirement. The requirement is we need to find the first and second objects by object types in the database and it should be in ascending order. 

The sample data will be created from the script below. Let us create it first, then we will demonstrate for the logic that need to be created as per requirement.

USE DEMO
GO

-- Creating sample table
IF(OBJECT_ID('TblCTEwithCTE','U') > 0)
  DROP TABLE TblCTEwithCTE

CREATE TABLE TblCTEwithCTE
(
 ObjectNumber INT ,
 ObjectType VARCHAR(50),
 ObjectName VARCHAR(100),
 ObjectCreateDate DATETIME
)

GO

-- Inserting sample records created above
INSERT INTO TBLCTEWITHCTE
(
 ObjectNumber,
 ObjectType,
 ObjectName,
 ObjectCreateDate
)
SELECT 
 ROW_NUMBER() OVER(PARTITION BY TYPE_DESC ORDER BY TYPE_DESC,CREATE_DATE) as ObjectNumber,
 TYPE_DESC,
 NAME,
 CREATE_DATE 
FROM SYS.OBJECTS

Here we are creating first CTE to get only objects with creating first based or created date by object type categories.

-- Fetching first created objects
;WITH FirstCreatedObjectsCTE
AS
(
 SELECT 
  ObjectNumber as ObjectNumber ,
  ObjectType as ObjectType, 
  ObjectName as FirstCreatedObject
 FROM TBLCTEWITHCTE WHERE ObjectNumber = 1
) 

SELECT 
 * 
FROM FirstCreatedObjectsCTE

GO

Now we have completed work for the first created objects by object type categories. And it is time to have the second created objects using first CTE and another second CTE to finally come out with an output which having both first created and next created objects. This first created and next created object by object type categories will be shown as column as follows.

-- Original table data.
SELECT 
 ObjectNumber,
 ObjectType,
 ObjectName,
 ObjectCreateDate
FROM TBLCTEWITHCTE
GO

-- Fetching first created objects in first CTE and using in second CTE for the second created objects.
;WITH FirstCreatedObjectsCTE
AS
(
 SELECT 
  ObjectNumber as ObjectNumber ,
  ObjectType as ObjectType, 
  ObjectName as FirstCreatedObject
 FROM TBLCTEWITHCTE WHERE ObjectNumber = 1
) 
,

SecondCreatedObjectsCTE
AS
(
 SELECT 
  t.ObjectType as ObjectType,
  c.FirstCreatedObject as FirstCreatedObject,
  t.ObjectName as SecondCreatedObject
 FROM TBLCTEWITHCTE t 
  RIGHT OUTER JOIN 
 FirstCreatedObjectsCTE c
  ON (c.ObjectType = t.ObjectType and t.ObjectNumber = c.ObjectNumber + 1)
)

SELECT 
 * 
FROM SecondCreatedObjectsCTE

GO

Hope you like this, stay tuned from more.