[sql] Can you create nested WITH clauses for Common Table Expressions?

WITH y AS (
    WITH x AS (
        SELECT * FROM MyTable
    )
    SELECT * FROM x
)
SELECT * FROM y

Does something like this work? I tried it earlier but I couldn't get it to work.

This question is related to sql sql-server tsql nested common-table-expression

The answer is


These answers are pretty good, but as far as getting the items to order properly, you'd be better off looking at this article http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge

Here's an example of his query.

WITH paths AS ( 
    SELECT 
        EmployeeID, 
        CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath 
    FROM EmployeeHierarchyWide 
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT 
        ehw.EmployeeID, 
        CONVERT(VARCHAR(900), CONCAT(p.FullPath, ehw.EmployeeID, '.')) AS FullPath 
    FROM paths AS p 
        JOIN EmployeeHierarchyWide AS ehw ON ehw.ManagerID = p.EmployeeID 
) 
SELECT * FROM paths order by FullPath

You can do the following, which is referred to as a recursive query:

WITH y
AS
(
  SELECT x, y, z
  FROM MyTable
  WHERE [base_condition]

  UNION ALL

  SELECT x, y, z
  FROM MyTable M
  INNER JOIN y ON M.[some_other_condition] = y.[some_other_condition]
)
SELECT *
FROM y

You may not need this functionality. I've done the following just to organize my queries better:

WITH y 
AS
(
  SELECT * 
  FROM MyTable
  WHERE [base_condition]
),
x
AS
(
  SELECT * 
  FROM y
  WHERE [something_else]
)
SELECT * 
FROM x

With does not work embedded, but it does work consecutive

;WITH A AS(
...
),
B AS(
...
)
SELECT *
FROM A
UNION ALL
SELECT *
FROM B

EDIT Fixed the syntax...

Also, have a look at the following example

SQLFiddle DEMO


Nested 'With' is not supported, but you can always use the second With as a subquery, for example:

WITH A AS (
                --WITH B AS ( SELECT COUNT(1) AS _CT FROM C ) SELECT CASE _CT WHEN 1 THEN 1 ELSE 0 END FROM B --doesn't work
                SELECT CASE WHEN count = 1 THEN 1 ELSE 0 END AS CT FROM (SELECT COUNT(1) AS count FROM dual)
                union all
                select 100 AS CT from dual
           )
              select CT FROM A

we can create nested cte.please see the below cte in example

;with cte_data as 
(
Select * from [HumanResources].[Department]
),cte_data1 as
(
Select * from [HumanResources].[Department]
)

select * from cte_data,cte_data1

I was trying to measure the time between events with the exception of what one entry that has multiple processes between the start and end. I needed this in the context of other single line processes.

I used a select with an inner join as my select statement within the Nth cte. The second cte I needed to extract the start date on X and end date on Y and used 1 as an id value to left join to put them on a single line.

Works for me, hope this helps.

cte_extract
as 
(
    select ps.Process as ProcessEvent
        , ps.ProcessStartDate 
        , ps.ProcessEndDate 
        -- select strt.*
    from dbo.tbl_some_table ps 
    inner join (select max(ProcessStatusId) ProcessStatusId 
                    from dbo.tbl_some_table 
                where Process = 'some_extract_tbl' 
                and convert(varchar(10), ProcessStartDate, 112) < '29991231'
                ) strt on strt.ProcessStatusId = ps.ProcessStatusID
), 
cte_rls
as 
(
    select 'Sample' as ProcessEvent, 
     x.ProcessStartDate, y.ProcessEndDate  from (
    select 1 as Id, ps.Process as ProcessEvent
        , ps.ProcessStartDate 
        , ps.ProcessEndDate
        -- select strt.*
    from dbo.tbl_some_table ps 
    inner join (select max(ProcessStatusId) ProcessStatusId 
                    from dbo.tbl_some_table 
                where Process = 'XX Prcss' 
                and convert(varchar(10), ProcessStartDate, 112) < '29991231'
                ) strt on strt.ProcessStatusId = ps.ProcessStatusID
    ) x
    left join (
        select 1 as Id, ps.Process as ProcessEvent
            , ps.ProcessStartDate 
            , ps.ProcessEndDate
            -- select strt.*
        from dbo.tbl_some_table ps 
        inner join (select max(ProcessStatusId) ProcessStatusId
                    from dbo.tbl_some_table 
                    where Process = 'YY Prcss Cmpltd' 
                    and convert(varchar(10), ProcessEndDate, 112) < '29991231'
                    ) enddt on enddt.ProcessStatusId = ps.ProcessStatusID
            ) y on y.Id = x.Id 
),

.... other ctes


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to nested

Nested routes with react router v4 / v5 Extract first item of each sublist python "TypeError: 'numpy.float64' object cannot be interpreted as an integer" How can I combine multiple nested Substitute functions in Excel? Retrieving values from nested JSON Object MySQL Nested Select Query? List comprehension on a nested list? Nested ifelse statement Single Line Nested For Loops Nested or Inner Class in PHP

Examples related to common-table-expression

SQL Server CTE and recursion example getting "No column was specified for column 2 of 'd'" in sql server cte? Update records in table from CTE How to create Temp table with SELECT * INTO tempTable FROM CTE Query Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable? The maximum recursion 100 has been exhausted before statement completion When to use Common Table Expression (CTE) Combining INSERT INTO and WITH/CTE Keeping it simple and how to do multiple CTE in a query Can you create nested WITH clauses for Common Table Expressions?