How we can use CTE in subquery in sql server?

Viewed 51719

How we can use a CTE in a subquery in SQL Server?

like:

SELECT id (I want to use CTE here), name FROM table_name

3 Answers

Just define your CTE on top and access it in the subquery?

WITH YourCTE(blubb) AS
(
    SELECT 'Blubb'
)
SELECT id,
       (SELECT blubb FROM YourCTE),
       name
FROM   table_name

Create a view with CTE/ Multiple CTEs with UNION sets of all CTEs

CREATE VIEW [dbo].[_vEmployees] 
AS 
    WITH 
    TEST_CTE(EmployeeID, FirstName, LastName, City, Country)
        AS (
            SELECT EmployeeID, FirstName, LastName, City, Country FROM Employees WHERE EmployeeID = 4
        ), 
    TEST_CTE2
        AS (
            SELECT EmployeeID, FirstName, LastName, City, Country FROM Employees WHERE EmployeeID = 7
        )
    SELECT EmployeeID, FirstName, LastName, City, Country FROM TEST_CTE UNION SELECT * FROM TEST_CTE2
GO

enter image description here

Now, use it into sub query

SELECT * FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM _vEmployees)

enter image description here

Related