Merging 3 query results

  • Dear all experts,

    This is Nayan. Below is my scenario: (SQL Server 2005)

    ------------------------------------

    A stored procedure has following statements:

    declare @var table

    (id varchar(5), name varchar(20), marks1, marks2, marks3)

    Select col1, col2, col3 from table1

    Select col1, col2, col3 from table2

    Select col1, col2, col3 from table3

    execution of the above stored procedure results as:

    ------------------

    1000 test1 10

    1001 test2 20

    1002 test3 30

    ***********

    1000 test1 20

    1005 test5 10

    ***********

    1000 test1 30

    1006 test6 10

    ------------------

    what I want to do is merge these three recordsets in @var and result should be as:

    -----------------------------------------------------------------------

    1000 test1 10 20 30

    1001 test2 20 -- --

    1002 test3 30 -- --

    1005 test5 -- 10 --

    1006 test6 -- -- 10

    -----------------------------------------------------------------------

    I hope this makes sense.

    Presently I am doing it within C#, but, I think its taking to much of time after the execution of the stored procedure, and I want to do it within the stored procedure itself and just send the results to C# app.

    I cannot just write one query as it depends upon different conditional statements than others.

    Can anyone please help me out. That would be a great help.

    Regards,

    Nayan

  • Hi mate,

    Try the UNION ALL command between the select statements:

    declare @var table

    (id varchar(5), name varchar(20), marks1, marks2, marks3)

    Select col1, col2, col3 from table1

    UNION ALL

    Select col1, col2, col3 from table2

    UNION ALL

    Select col1, col2, col3 from table3

    Keep in mind that you will have to keep the column names the same. I hope this fits the context of what you're after.

    Good luck! - DamienB

  • Thanks Damien for your reply.

    But, I cannot use union all as there would be duplicate rows all three result sets and only the last column (marks - col3) would be different.

    the priority of the results would be: Resultset 1, Resultset 2, Resultset 3

    if the id(1000,1002...) exists within Resultset 1, then I just need to add the value of marks in @var for that id, and if the id (1000,1002...) does not exist, then I add the new row in @var with the marks value.

    Does this make sense. I think the example given in the first post describes this. I want to merge the records if they are same in the resultsets based on ID (1000, 1001, 1002...) and if not same, add a new record with that ID.

    Thanks.

  • aah I see, I think I should make up a new acronym: "RTFQ", I should have paid better attention to your result set 🙂

    I still dont understand your reference to col1, col2 col3, but this code might give you some ideas on how to tackle your problem. I replicated your demonstration data inside my own SQL server and wrote this:

    select distinct(rs1.id), rs1.name,

    marks1 = case when t1.marks1 is null then '--' else t1.marks1 end,

    marks2 = case when t2.marks1 is null then '--' else t2.marks1 end,

    marks3 = case when t3.marks1 is null then '--' else t3.marks1 end

    from

    (select id, name from table1

    union all

    select id, name from table2

    union all

    select id, name from table3

    ) as rs1

    left join table1 as t1 on t1.id = rs1.id

    left join table2 as t2 on t2.id = rs1.id

    left join table3 as t3 on t3.id = rs1.id

    To come up with:

    1000test110 20 30

    1001test220 -- --

    1002test330 -- --

    1005test5-- 10 --

    1006test6-- -- 10

    Hope this is some help 🙂 If this is a failure, I think I'll leave it up to a pro to cast his or her eyes over it 😉 - DamienB

  • Hey Damien,

    You are a champion. This is exatly what I want. Why it did not come to my mind... A very big and heavy thanks to you.

    I would have to check now whether it does not eat more of my resources... execution plans and from the app point of view as well..

    Thanks again.

    Regards

    Nayan

  • Just for fun eh!

    Isn't it a case of pivoting?

    Test script and data:

    drop table #t1

    drop table #t2

    drop table #t3

    Create table #t1(col1 int, col2 varchar(10), col3 int)

    Create table #t2(col1 int, col2 varchar(10), col3 int)

    Create table #t3(col1 int, col2 varchar(10), col3 int)

    INSERT INTO #t1 VALUES (1000,'test1',10)

    INSERT INTO #t1 VALUES (1001,'test2',20)

    INSERT INTO #t1 VALUES (1002,'test3',30)

    INSERT INTO #t2 VALUES (1000,'test1',20)

    INSERT INTO #t2 VALUES (1005,'test5',10)

    INSERT INTO #t3 VALUES (1000,'test1',20)

    INSERT INTO #t3 VALUES (1006,'test6',10)

    Crosstab:

    SELECTT4.col1,

    T4.col2,

    SUM(CASE WHEN resset =1 THEN col3 END) as MARK1,

    SUM(CASE WHEN resset =2 THEN col3 END) as MARK2,

    SUM(CASE WHEN resset =3 THEN col3 END) as MARK3

    FROM (Select col1, col2, col3, 1 as resset from #t1

    UNION ALL

    Select col1, col2, col3,2 as resset from #t2

    UNION ALL

    Select col1, col2, col3, 3 as resset from #t3) T4

    Group By col1, col2

    PIVOT:

    SELECTcol1,

    col2,

    [1] as MARK1,

    [2] as MARK2,

    [3] as MARK3

    FROM (Select col1, col2, col3, 1 as resset from #t1

    UNION ALL

    Select col1, col2, col3,2 as resset from #t2

    UNION ALL

    Select col1, col2, col3, 3 as resset from #t3) T4

    PIVOT (SUM(col3) FOR resset IN ([1], [2], [3])) as pvt

    ORDER BY col1

    Not sure if it exactly suits your requirement though!

    ---------------------------------------------------------------------------------

  • Thanks for the additional info mate! I've never got my head around pivioting, but you've provided a great working example for me to give it a go 🙂

    - DamienB

  • You may want to avoid PIVOTs in favor of the old fashioned but tried and true crosstab after reading the following article...

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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