How do I union multiple WITH clause in sql server 2008?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi members,

For example I have 2 or more WITH clause statement something like below:
How do I union multiple WITH clause statements together? thanks

SQL

--WITH clause 1

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

--WITH clause 2

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS

(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

--WITH clause 3

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS

(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

Posted 1-Oct-15 14:43pm

waynetan123

Updated 8-Jun-23 2:43am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-10-01T17:16:00

As far as I know you can't use UNION on two separate CTE's.
Some options you have:
- Place queries inside a single CTE and use union inside the one CTE
- Don't use CTE but 'traditional' SELECT statements
- Create a view for a single CTE and combine them

Examples
All-in

SQL

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    UNION ALL
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL

)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

No CTE

SQL

SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
UNION ALL
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL

View

SQL

CREATE VIEW Sales1 AS
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
 
CREATE VIEW Sales2 AS
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
 
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

SELECT SalesPersonID,TotalSales, SalesYear FROM Sales1
UNION 
SELECT SalesPersonID,TotalSales, SalesYear FROM Sales2

Member 16024872 · Answer 2 · 2023-06-08T02:43:00

Solution 2

select * from (with1 ***) t1
union all
select * from (with2 ***) t2

Posted 8-Jun-23 2:43am

Member 16024872

Comments

CHill60 8-Jun-23 12:21pm

Quite apart from the fact the question was answered nearly 8 years ago, your suggestion is syntactically incorrect and will generate an error "Incorrect syntax near the keyword 'WITH'"
Perhaps you meant

WITH with1 AS (***),
with2 AS (***)
SELECT * from with1
UNION
SELECT * from with2;

Member 16024872 9-Jun-23 8:59am

your code's incorrect.
i did mean this:
select * from (with temp1 as (select * from foo) ) t1
union all
select * from (with temp2 as (select * from foo) ) t2