Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


is it possible to union all two CTEs?


is it possible to union all two CTEs?

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24255 Visits: 37978
Once again, you really don't need to use CTEs in this example. I am posting my code again, but this time I have included a version using CTEs. If the order of the data from each CTE is also important, add a "rank" column like Matt Miller did in his code and include that in the ORDER BY clause along with the Name column.

The "rank" column can be added to each individual query (my original code) or in the CTEs.



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

with cte1 as (
select
Name,
'Count' as Category,
count(Name) as Value
from
#temp
group by
Name
), cte2 as (
select
Name,
'Duration' as Category,
sum(Duration) as Value
from
#temp
group by
Name
)
select * from cte1
union all
select * FROM CTE2
order by
Name;
go

drop table #temp;
go




Cool
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)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8566 Visits: 18143
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 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.


The easiest way would be to use a SELECT...INTO... for each set of CTEs and then UNION your temp tables.
Otherwise, it's hard to give a good answer without all the details.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
Luis thanks, I decided to go with that solution this morning. Appreciate it.
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
Matt, thank you for your answers. I didn't know '=' sign is being deprecated. Will look into alternatives. As far as order is concerned, I'm not in need of ordering....it's truly relational set I'm after in the results.

I will try to rewrite my queries without CTE's so that I do not have problem with combining results. Selecting into the destination table two times is sufficient for now...I will post better DDL in separate thread for help redoing the queries if I need help.

Thanks for the help.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24255 Visits: 37978
Matt Miller (#4) (5/1/2013)
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.


Not completely true regarding the = being depcrecated in SQL Server.

This is being deprecrated:

A string enclosed in quotation marks used as a column alias for an expression in a SELECT list:
'string_alias' = expression

The following can be used:

expression [AS] column_alias
expression [AS] [column_alias]
expression [AS] "column_alias"
expression [AS] 'column_alias'
column_alias = expression

Cool
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)
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5848 Visits: 11407
I recon Lynn's code can be simplified.
Since both CTE's use the same grouping they may be merged in one:


with cte as (
select
Name,
count(Name) as [Count],
sum(Duration) as Duration
from #temp
group by Name
)
select Name, 'Count' as Category, [Count] as [Value]
from cte
union all
select Name, 'Duration' as Category, Duration as [Value]
FROM cte
order by Name;
go



But since the code in CTE is re-executed each time the CTE is referenced I have to support Lynn here - CTE does not provide any advantage.
Except, probably, more pleasant look of the query.

If you have many categories to display you better go with a temp table in place of CTE.
Then the base table will be queried just once.
And all UNION parts will be reading the #temp resultset cached in memory.
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: General Forum Members
Points: 591 Visits: 898
My 2 Cents worth,
Seeing as Count(tickets) and Sum(duation) are both aggregates on the same dataset with the same grouping, why not do them in the one CTE and then UNPIVOT() the results
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search