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 Wednesday, May 01, 2013 9:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
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
Post #1448461
Posted Wednesday, May 01, 2013 9:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 6,748, Visits: 12,843
How about a sample table script?

“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 #1448468
Posted Wednesday, May 01, 2013 9:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 22,475, Visits: 30,155
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1448473
Posted Wednesday, May 01, 2013 9:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
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.
Post #1448485
Posted Wednesday, May 01, 2013 9:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 22,475, Visits: 30,155
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1448491
Posted Wednesday, May 01, 2013 9:42 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
Lynn, ***please***, I have not oversimplified the problem. The problem is to use CTEs. You are providing an alternate to CTEs, which I am not asking for unless someone can produce a result set that I am after (yours doesn't).

My original DDL is sufficient for demonstrating two sets of CTEs, each of which produce a result set, which I want to combine into one.
Post #1448494
Posted Wednesday, May 01, 2013 9:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 22,475, Visits: 30,155
Based on your original post, the correct code is 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
)
select * from cte
union all
select * from cte;



The cte's must defined prior to the selects and both cte's are identical therefore only one is needed.

Now, how about providing the actual problem you are trying to solve.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1448496
Posted Wednesday, May 01, 2013 9:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 22,475, Visits: 30,155
Your original code:


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 -- << error is here
(select * from #temp)
select * from cte


The error message:

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


If you double click on the error message in the messages tab it takes you directly to the error.

Also, CTE's do not start with a semicolon. The semicolon is a statement terminiator not a statement begininator.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1448507
Posted Wednesday, May 01, 2013 9:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
Please accept that the question evolved a little. If you would prefer, I will open a new thread.
I've added a third cte per script; In real life situation I have to use three CTEs per script for the results I'm after. I can't post real scripts. How would I UNION ALL two scripts, which utilize three CTEs each.

The reason I have 3 CTEs per script is that I need to create a column called Category, to alternately contain 'Count' or 'Duration' and I need to show counts and duration for each name. I didn't find a way to do this using one script.

;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
Post #1448509
Posted Wednesday, May 01, 2013 10:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 7,081, Visits: 14,672
I think you're looking for something like this.

;with ct1 as
(select
name
,Duration, 2 as rank
from #temp
), ct2 as
(
select
name
, sum(Duration) as value
, 'Duration' as category,
, 1 as rank
from ct1
group by name
)
select
name
, Category
, Value
, null as Memo
from ct2
union all
select name,
'Count' as category,
value,
null as memo
from CTE1
order by name,rank

A few notes:
- because you're doing a union (even if it's a union all), you need to be disciplined to make sure that both parts have the same names and that they're in the same order within the query.
- using the = in a select to create an alias is being deprecated, you really want to avoid that notation if possible.
- the rows won't just order themselves without you telling them HOW to be ordered correctly, so structure the order by accordingly. Remember that there is no such thing as physical or natural order in databases, so you have to be explicit about the ordering.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1448525
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse