SQL Clone
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-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39461 Visits: 38552
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16593 Visits: 19098
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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1529 Visits: 1905
Luis thanks, I decided to go with that solution this morning. Appreciate it.
KoldCoffee
KoldCoffee
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1529 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-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39461 Visits: 38552
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10376 Visits: 11961
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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 902
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