May 1, 2013 at 9:42 am
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.
May 1, 2013 at 9:45 am
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.
May 1, 2013 at 9:51 am
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.
May 1, 2013 at 9:54 am
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
May 1, 2013 at 10:55 am
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?
May 1, 2013 at 12:04 pm
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
May 1, 2013 at 7:55 pm
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.
May 1, 2013 at 8:30 pm
Luis thanks, I decided to go with that solution this morning. Appreciate it.
May 1, 2013 at 8:37 pm
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.
May 1, 2013 at 9:25 pm
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
May 2, 2013 at 8:10 pm
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.
_____________
Code for TallyGenerator
May 8, 2013 at 10:26 am
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
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply