SQL Server – Multiple CTE in One SELECT Statement Query

I have previously written many articles on CTE. One question I get often is how to use multiple CTE in one query or multiple CTE in SELECT statement. Let us see quickly two examples for the same. I had done my best to take simplest examples in this subject.

Option 1 :

/* Method 1 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT 2 AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO

Option 2:

/* Method 2 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT COL1+1 AS Col2 FROM CTE1)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO

Please note the difference between options. If there is any other option than above mentioned two options, please leave your comment here.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Humorous SQL Cake – Funny SQL Cake
Next Post
SQLAuthority News – Interview with SQL Server MVP Glenn Berry

Related Posts

73 Comments. Leave new

  • Do we have WITH clause in SQL Server 2008 R2

    Reply
  • can i fire query like this in sql server 2008

    Select * from emp;

    please suggest me i am new to sql server 2008.

    Reply
  • thank you again…

    Reply
  • Use SELECT TOP 99.99999999999 PERCENT * FROM….. It worked for me…. It gives the same COUNT comparing with simple SELECT * FROM statement….

    Reply
  • Looking all over for this answer and this comes close, how do I incorporate this CTE:

    SELECT TarnScore.CGCODINGMASTEROID, TarnScore.Score
    AS “TARN Score”

    FROM (
    SELECT DISTINCT ST2.CGCODINGMASTEROID,
    (
    SELECT SUM(ted.TARNSCORE)
    FROM HealthBI.dbo.LZO_CGCODINGDETAIL AS ST1
    LEFT join CommDB.dbo.tblTARNEDCodes as ted on ST1.DESCRIPTION = ted.DESCRIPTION
    WHERE ST1.CGCODINGMASTEROID = ST2.CGCODINGMASTEROID
    AND ted.[TARN Possible] ‘None’
    —ORDER BY ST1.CGCODINGMASTEROID
    ) [Score]

    FROM HealthBI.dbo.LZO_CGCODINGDETAIL ST2

    ) AS [TarnScore]

    Into this SQL Code (note its the final bit where this look similar and the join would occur I think)

    ;WITH CTE
    AS (
    SELECT ed.AttendDateTime AS [ED Attend Date],
    ip.HEYNo AS [HEY Number],
    ip.NHSNo1 AS [NHS Number],
    ip.Forename,
    ip.Surname,
    ip.DOB,
    ed.ComplaintFreetext AS [Additional Comments],
    ed.LOCAL_ACCIDENT_AND_EMERGENCY_DIAGNOSIS_DESCRIPTION,
    ed.StreamDesc AS [Major/Minor/Paeds],
    CASE WHEN ip.DateOfDeath IS NULL THEN ‘N’ ELSE ‘Y’ END AS [Died In Hospital],
    ed.DepartDateTime,
    ip.AdmitDateTime,
    CASE WHEN ip.DischDateTime IS NULL THEN ‘N’ ELSE ‘Y’ END AS [Discharged?],
    ip.DischDateTime AS [Discharge Date/Time],
    ed.EDAttendanceOID,
    ce.IDENTIFYINGOID,
    ce.IDENTIFYINGTYPE,
    cm.OID AS [CodingMasterOID],
    e.OID AS [EncounterOID]
    FROM dbo.ED_ATTENDANCE AS ed
    LEFT JOIN dbo.IP_ADMISSION AS ip ON ed.MFPatientID = ip.MFPatientID
    INNER JOIN HealthBI.dbo.LZO_AEATTENDANCE AS lae ON ed.EDAttendanceOID = lae.OID
    INNER JOIN HealthBI.dbo.LZO_ENCOUNTER AS e ON lae.ENCOUNTEROID = e.OID
    INNER JOIN HealthBI.dbo.LZO_PATIENTCAREEVENTS AS ce ON e.OID = ce.ENCOUNTEROID
    AND ce.IDENTIFYINGTYPE = ‘Create Attendence’
    LEFT JOIN HealthBI.dbo.LZO_CGCODINGMASTER AS cm ON ce.IDENTIFYINGOID = cm.IDENTIFYINGOID
    AND ce.IDENTIFYINGTYPE = cm.IDENTIFYINGTYPE

    —- ok to here
    WHERE ed.AttendDate >= ’16 january 2016′
    AND ed.AttendDate < '17 january 2016'
    AND (DATEDIFF(HOUR, ed.DepartDateTime, ip.AdmitDateTime) BETWEEN – 5 AND 5)
    AND (ed.HospitalCode = 'RWA01')
    )
    —————————————-
    — below concatinates multiple rows/fields into one long string
    SELECT Main.CGCODINGMASTEROID,
    Left(Main.DESCRIPTIONS, Len(Main.DESCRIPTIONS) – 1) AS "ED Descriptions",
    c.*

    FROM (SELECT DISTINCT ST2.CGCODINGMASTEROID,
    (SELECT ST1.DESCRIPTION + ', ' AS [text()]
    FROM HealthBI.dbo.LZO_CGCODINGDETAIL AS ST1
    LEFT join CommDB.dbo.tblTARNEDCodes as ted on ST1.DESCRIPTION = ted.DESCRIPTION
    WHERE ST1.CGCODINGMASTEROID = ST2.CGCODINGMASTEROID
    AND ted.[TARN Possible] ‘None’
    ORDER BY ST1.CGCODINGMASTEROID
    FOR XML PATH(”)
    ) [DESCRIPTIONS]
    FROM HealthBI.dbo.LZO_CGCODINGDETAIL ST2

    ) AS [Main]
    RIGHT JOIN CTE AS c ON Main.CGCODINGMASTEROID = c.CodingMasterOID

    Reply
  • Shantanu Chakraborty
    March 1, 2016 3:12 pm

    I need the SQLserver 2005 program or query for the below mentioned table (date diff)

    SEQ1     PRCS_DT    SEQ2
        1 2012-07-22 0
        2 2012-07-23 1
        3 2012-07-24 1
        4 2012-07-26 2
        5 2012-07-29 3

    Reply
  • How do I convert a string to number in a store procedure that uses an open query

    Reply

Leave a Reply

Menu