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.