April 30, 2013 at 6:44 pm
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?
April 30, 2013 at 7:47 pm
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)
April 30, 2013 at 10:03 pm
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/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
April 30, 2013 at 11:48 pm
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.
May 1, 2013 at 1:43 am
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
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
May 1, 2013 at 6:12 am
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.
May 1, 2013 at 6:20 am
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
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
May 1, 2013 at 7:00 am
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?
May 1, 2013 at 7:04 am
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?
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
May 1, 2013 at 7:06 am
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!"-)
May 1, 2013 at 9:07 am
I've added a third cte per script; In real life situation I have to use three per script for the results I'm after. How would I UNION ALL these two (as an alternate to malwa's appreciated working solution using temp table)?
;with ct1 as
(select
name
,Value = COUNT(ticketID)
from #temp
group by Name
), ct2 as
(
select
name
, Value
, Category = 'Count'
from ct1
)
select
name
, Category
, Value
, Memo = NULL
from ct2
-------
;with ct1 as
(select
name
,Duration
from #temp
), ct2 as
(
select
name
, Value= (Select sum(Duration))
, Category = 'Duration'
from ct1
group by name
)
select
name
, Category
, Value
, Memo = NULL
from ct2
Results to look as follows:
Name | Category | Value | Memo
Blake | Count | 2 | NULL
Blake | Duration | 62 | NULL
Helen | Count | 2 | NULL
Helen | Duration | 28 | NULL
Joe | Count | 2 | NULL
Joe | Duration | 33 | NULL
May 1, 2013 at 9:15 am
How about a sample table script?
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
May 1, 2013 at 9:22 am
No CTEs are needed:
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);
go
select
Name,
'Count' as Category,
count(Name) as Value
from
#temp
group by
Name
union all
select
Name,
'Duration' as Category,
sum(Duration) as Value
from
#temp
group by
Name
order by
Name;
go
drop table #temp;
go
May 1, 2013 at 9:32 am
please don't take the sample scripts to literally. They are over simplified, so my question is still... how to combine the results of two scripts sharing identical columns, each made up of several CTEs (as posted above)? I am not trying to UNION ALL two ctes but rather two sets of CTEs.
May 1, 2013 at 9:39 am
KoldCoffee (5/1/2013)
please don't take the sample scripts to literally. They are over simplified, so my question is still... how to combine the results of the CTEs? Actually, Malleswarareddy_m already answered with working answer, but Chris had another angle and I wondered if it would work for me.
Best answer I have for this, don't over simplify your question. Provide us with what we need to give you a proper answer.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply