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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:06 PM
Points: 658, Visits: 1,541
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: Monday, July 21, 2014 3:43 AM
Points: 1,939, Visits: 1,162
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:06 PM
Points: 658, Visits: 1,541
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 @ 8:09 AM
Points: 6,750, Visits: 13,896
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:06 PM
Points: 658, Visits: 1,541
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 @ 8:09 AM
Points: 6,750, Visits: 13,896
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:06 PM
Points: 658, Visits: 1,541
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 @ 8:09 AM
Points: 6,750, Visits: 13,896
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:06 PM
Points: 658, Visits: 1,541
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