is it possible to union all two CTEs?

  • I have two CTEs sharing same column names and I need to combine their results into one result set.

    Given my DDL below, one would conclude is isn't possible, but I am hoping there is a workaround.

    In the following you'll see that I am union all-ing two result sets with identical number of columns and column names. But, union all doesn't work. I get error:

    Msg 102, Level 15, State 1, Line 7

    Incorrect syntax near ';'.

    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

    (select * from #temp)

    select * from cte

    Is it possible to combine the results of two CTEs?

  • You can use multiple ctes like 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)

    ,

    cte1 as

    (

    select * from #temp

    union all

    select * from cte

    )

    select * from cte1

    for more details visit this site

    http://msdn.microsoft.com/en-us/library/ms175972.aspx

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • It looks like malleswarareddy_m's solution should work, but I guess it depends on what exactly are you trying to accomplish. How come you decided to use CTE's instead of temporary tables?

    --------
    For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
    You can also follow my twitter account to get daily updates: @BLantz2455

  • Thank you Malleswarareddy, for the answer.

    To answer your question, Calibear, below is DDL demonstrating my reason for needing to UNION ALL two CTEs.

    New DDL

    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);

    From this data I must produce a report showing two additional columns: Category, Value which label and summarize how many tickets were filed, per Name and the Duration of the tickets per Name. Like this:

    ;with ct1 as

    (select

    name

    ,Value = COUNT(ticketID)

    from #temp

    group by Name

    )

    select

    name

    , Value

    , Category = 'Count'

    from ct1

    -------

    ;with ct1 as

    (select

    name

    ,Duration

    from #temp

    )

    select

    name

    , Value= (Select sum(Duration))

    , Category = 'Duration'

    from ct1

    group by name

    -----

    Ideally, I could gets counts and duration by Name, with correct corresponding entry in Category column, with just one query, but I don't know how...so I've used CTEs.

  • create table #temp (ID int, Name varchar(20))

    insert into #temp

    values

    (10, 'Helen'),

    (20, 'Joe'),

    (30, 'Blake');

    ;WITH OnlyHelen AS (

    SELECT CTE = 1, *

    FROM #temp

    WHERE Name = 'Helen'

    ),

    ExceptHelen AS (

    SELECT CTE = 2, *

    FROM #temp

    WHERE Name <> 'Helen'

    )

    SELECT *

    FROM OnlyHelen

    UNION ALL

    SELECT *

    FROM ExceptHelen

    “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

  • Chris,

    thanks for reply. I should have posted what the result set was that I'm after. There should be two rows for each person, one recording total count of tickets and the other total duration of tickets. It should look like this:

    Name, Category, Value

    Helen | Count | 2

    Helen |Duration| 28

    Joe | Count | 2

    Joe |Duration| 33

    Blake | Count | 2

    Blake |Duration| 62.

    If Helen had another ticket, then count would go up to 3 and Duration would increase.

  • KoldCoffee (5/1/2013)


    Chris,

    thanks for reply. I should have posted what the result set was that I'm after. There should be two rows for each person, one recording total count of tickets and the other total duration of tickets. It should look like this:

    Name, Category, Value

    Helen | Count | 2

    Helen |Duration| 28

    Joe | Count | 2

    Joe |Duration| 33

    Blake | Count | 2

    Blake |Duration| 62.

    If Helen had another ticket, then count would go up to 3 and Duration would increase.

    ;with ct1 as (

    select

    name

    ,Value = COUNT(ticketID)

    , Category = 'Count'

    from #temp

    group by Name

    ),

    ct2 AS (

    select

    name

    , Value= (Select sum(Duration))

    , Category = 'Duration'

    from #temp

    group by name

    )

    SELECT *

    FROM ct1

    UNION ALL

    SELECT *

    FROM ct2

    ORDER BY Name

    “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

  • Oh yeah! Nice. Thanks Chris.

    In the real world case I have several CTEs in each statement. ie. ct1, followed by ct2, followed by final select. Which CTE do I call out?

  • KoldCoffee (5/1/2013)


    Oh yeah! Nice. Thanks Chris.

    In the real world case I have several CTEs in each statement. ie. ct1, followed by ct2, followed by final select. Which CTE do I call out?

    I'd rather not guess - can you post what you've got?

    “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

  • you are right. I'm running to catch bus and my attempt to make new ddl snafood. Shoulda quelched the temptation to be vague and hopeful! Will post.

    High Five!"-)

  • 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

  • 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

  • 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

  • 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.

  • 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.

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply