none
How to attach two query results? RRS feed

  • Question

  • I have the two below results when executing two queries consecutively
    65 AP
    66 SAT
    52 SAT II

    956 Art History
    1345 Biology
    931 Biology E

    I need to attach both the results in SQL as below

    65 AP       956 Art History
    66 SAT     1345 Biology
    52 SAT II  931 Biology E

    Can anyone pls help me to attach these queries..

    Than




    Wednesday, July 29, 2009 7:05 PM

Answers

  • ; with cte as
    (
        // your first query
    ),
    cte1 as
    (
      // your second query
    ),cte2 as
    (
        select col1,col2,row_number() over (order by Col2) as rn  from cte
    ),
    cte3 as
    (
       select col1,col2,row_number() over (order by Col2) as rn  from cte1
    )
    select  c2.col1,c2.col2,c3.col1,c3.col2 from cte2 c2 inner join cte3 c3 on c2.rn = c3.rn




    You can reduce the 2 ctes by using row_number() function directly when you are getting those query results.
    • Marked as answer by Zongqing Li Wednesday, August 5, 2009 9:18 AM
    Wednesday, July 29, 2009 7:10 PM

All replies

  • Just add the UNION clause between the two sql statements
    Anthony Martin | www.emc.com/mspractice
    Wednesday, July 29, 2009 7:06 PM
    Answerer
  • ; with cte as
    (
        // your first query
    ),
    cte1 as
    (
      // your second query
    ),cte2 as
    (
        select col1,col2,row_number() over (order by Col2) as rn  from cte
    ),
    cte3 as
    (
       select col1,col2,row_number() over (order by Col2) as rn  from cte1
    )
    select  c2.col1,c2.col2,c3.col1,c3.col2 from cte2 c2 inner join cte3 c3 on c2.rn = c3.rn




    You can reduce the 2 ctes by using row_number() function directly when you are getting those query results.
    • Marked as answer by Zongqing Li Wednesday, August 5, 2009 9:18 AM
    Wednesday, July 29, 2009 7:10 PM
  • jumped the gun on my answer, saw attach and assumed wrong.  reamireddy's solution will work
    Anthony Martin | www.emc.com/mspractice
    Wednesday, July 29, 2009 7:32 PM
    Answerer