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 1, 2013 12:04 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 20,739, Visits: 32,528
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





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 #1448552
Posted Wednesday, May 1, 2013 7:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 3,784, Visits: 8,489
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1448660
Posted Wednesday, May 1, 2013 8:30 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: Tuesday, October 21, 2014 9:00 PM
Points: 680, Visits: 1,598
Luis thanks, I decided to go with that solution this morning. Appreciate it.
Post #1448664
Posted Wednesday, May 1, 2013 8:37 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: Tuesday, October 21, 2014 9:00 PM
Points: 680, Visits: 1,598
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.
Post #1448668
Posted Wednesday, May 1, 2013 9:25 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 20,739, Visits: 32,528
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




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 #1448671
Posted Thursday, May 2, 2013 8:10 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:25 PM
Points: 4,573, Visits: 8,354
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.
Post #1449029
Posted Wednesday, May 8, 2013 10:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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

Post #1450681
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse