Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

is it possible to union all two CTEs? Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 6:44 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 704, Visits: 1,656
I have two CTEs sharing same column names and I need to combine their results into one result set.
Given my DDL below, one would conclude is isn't possible, but I am hoping there is a workaround.

In the following you'll see that I am union all-ing two result sets with identical number of columns and column names. But, union all doesn't work. I get error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ';'.

create table #temp (ID int, Name varchar(20))
insert into #temp
values
(10, 'Helen'),
(20, 'Joe'),
(30, 'Blake');

;with cte as
(select * from #temp)
select * from cte

union all

;with cte as
(select * from #temp)
select * from cte



Is it possible to combine the results of two CTEs?
Post #1448284
Posted Tuesday, April 30, 2013 7:47 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:09 PM
Points: 1,940, Visits: 1,173
You can use multiple ctes like this

create table #temp (ID int, Name varchar(20))
insert into #temp
values
(10, 'Helen'),
(20, 'Joe'),
(30, 'Blake');

;with cte as
(select * from #temp)
,
cte1 as
(
select * from #temp
union all
select * from cte
)
select * from cte1

for more details visit this site

http://msdn.microsoft.com/en-us/library/ms175972.aspx


Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1448289
Posted Tuesday, April 30, 2013 10:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
It looks like malleswarareddy_m's solution should work, but I guess it depends on what exactly are you trying to accomplish. How come you decided to use CTE's instead of temporary tables?

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1448302
Posted Tuesday, April 30, 2013 11:48 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 704, Visits: 1,656
Thank you Malleswarareddy, for the answer.

To answer your question, Calibear, below is DDL demonstrating my reason for needing to UNION ALL two CTEs.

New DDL
create table #temp (TicketID int, Name varchar(20), Duration int)
insert into #temp
values
(10, 'Helen', 8),
(15, 'Blake', 12),
(20, 'Joe', 17),
(25, 'Joe', 16),
(30, 'Blake', 50),
(35, 'Helen', 20);


From this data I must produce a report showing two additional columns: Category, Value which label and summarize how many tickets were filed, per Name and the Duration of the tickets per Name. Like this:
;with ct1 as
(select
name
,Value = COUNT(ticketID)
from #temp
group by Name
)
select
name
, Value
, Category = 'Count'
from ct1

-------
;with ct1 as
(select
name
,Duration
from #temp
)
select
name
, Value= (Select sum(Duration))
, Category = 'Duration'
from ct1
group by name

-----
Ideally, I could gets counts and duration by Name, with correct corresponding entry in Category column, with just one query, but I don't know how...so I've used CTEs.
Post #1448312
Posted Wednesday, May 1, 2013 1:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
create table #temp (ID int, Name varchar(20))
insert into #temp
values
(10, 'Helen'),
(20, 'Joe'),
(30, 'Blake');

;WITH OnlyHelen AS (
SELECT CTE = 1, *
FROM #temp
WHERE Name = 'Helen'
),
ExceptHelen AS (
SELECT CTE = 2, *
FROM #temp
WHERE Name <> 'Helen'
)
SELECT *
FROM OnlyHelen
UNION ALL
SELECT *
FROM ExceptHelen



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1448331
Posted Wednesday, May 1, 2013 6:12 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 704, Visits: 1,656
Chris,
thanks for reply. I should have posted what the result set was that I'm after. There should be two rows for each person, one recording total count of tickets and the other total duration of tickets. It should look like this:

Name, Category, Value
Helen | Count | 2
Helen |Duration| 28
Joe | Count | 2
Joe |Duration| 33
Blake | Count | 2
Blake |Duration| 62.

If Helen had another ticket, then count would go up to 3 and Duration would increase.
Post #1448382
Posted Wednesday, May 1, 2013 6:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
KoldCoffee (5/1/2013)
Chris,
thanks for reply. I should have posted what the result set was that I'm after. There should be two rows for each person, one recording total count of tickets and the other total duration of tickets. It should look like this:

Name, Category, Value
Helen | Count | 2
Helen |Duration| 28
Joe | Count | 2
Joe |Duration| 33
Blake | Count | 2
Blake |Duration| 62.

If Helen had another ticket, then count would go up to 3 and Duration would increase.


;with ct1 as (
select
name
,Value = COUNT(ticketID)
, Category = 'Count'
from #temp
group by Name
),
ct2 AS (
select
name
, Value= (Select sum(Duration))
, Category = 'Duration'
from #temp
group by name
)
SELECT *
FROM ct1
UNION ALL
SELECT *
FROM ct2
ORDER BY Name



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1448384
Posted Wednesday, May 1, 2013 7:00 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 704, Visits: 1,656
Oh yeah! Nice. Thanks Chris.

In the real world case I have several CTEs in each statement. ie. ct1, followed by ct2, followed by final select. Which CTE do I call out?
Post #1448400
Posted Wednesday, May 1, 2013 7:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
KoldCoffee (5/1/2013)
Oh yeah! Nice. Thanks Chris.

In the real world case I have several CTEs in each statement. ie. ct1, followed by ct2, followed by final select. Which CTE do I call out?


I'd rather not guess - can you post what you've got?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1448402
Posted Wednesday, May 1, 2013 7:06 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 704, Visits: 1,656
you are right. I'm running to catch bus and my attempt to make new ddl snafood. Shoulda quelched the temptation to be vague and hopeful! Will post.
High Five!"-)
Post #1448404
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse