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 as 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 objectes with created 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 to 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 output which having both first created and next created objects.
This first created and next created objects by object type categories will be shown as column as following.
-- 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 liked this, stay tuned from more.