locked
Combing multiple result sets from SELECT RRS feed

  • Question

  • Hi all,

    I have a SELECT statement which summarizes monthly sales info by payor groups.  The SELECT statement is structured in such a way that only one month worth of data can be selected into the result set.  I need to have 12 trailing months worth of data in the result set, grouped by payor group and month.  To do that, I created a WHILE loop so that the SELECT statement is ran 12 times for each of the 12 training months.  When I run the query, I see 12 result sets in SSMS.

    Is there a way to put result sets for the 12 months into a single result set (columns being month, payor group, sales)?  I tried to insert UNION before END of the WHILE statement, but SSMS gives me an error.

    Any help will be greatly appreciated.

    Thanks

    Igor

    Wednesday, November 10, 2010 10:05 PM

Answers

  • Create a temp table and insert into this table, e.g.

    if object_ID('tempdb..#Result') IS NOT NULL drop table #Result
    
    create table #Result --(here goes the structure matching the expected result)
    
    while @I <=@Iteraltions
    
    begin
    
       insert into #Result
    
       select ....
    
      set @i = @i + 1
    
    end
    
    select * from #Result
    

     

    However, there is also a possibility you can get desired result with just one select statement. If you provide more info, you can get a better answer.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Atif-ullah Sheikh Thursday, November 11, 2010 7:31 AM
    • Marked as answer by Igor_Proper Thursday, November 11, 2010 9:47 PM
    Wednesday, November 10, 2010 10:17 PM

All replies

  • try :

     select month, [payor group],sum(sales) from yourtable group by month,[payor group]

     


    Best regards
    Wednesday, November 10, 2010 10:14 PM
  • Create a temp table and insert into this table, e.g.

    if object_ID('tempdb..#Result') IS NOT NULL drop table #Result
    
    create table #Result --(here goes the structure matching the expected result)
    
    while @I <=@Iteraltions
    
    begin
    
       insert into #Result
    
       select ....
    
      set @i = @i + 1
    
    end
    
    select * from #Result
    

     

    However, there is also a possibility you can get desired result with just one select statement. If you provide more info, you can get a better answer.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Atif-ullah Sheikh Thursday, November 11, 2010 7:31 AM
    • Marked as answer by Igor_Proper Thursday, November 11, 2010 9:47 PM
    Wednesday, November 10, 2010 10:17 PM
  • try :

     select month, [payor group],sum(sales) from yourtable group by month,[payor group]

     


    Best regards


    It's better to use one query, and when you want to seach for one payor group, it's like this :

    select month, [payor group],sum(sales) from yourtable where [payor group]=@payg group by month,[payor group]
    

    Best regards
    Wednesday, November 10, 2010 10:24 PM
  • Naom, thanks for the info and the suggestion.  It actually worked by using a temp table.

    Badii, I was planning to use a single SELECT statement, but as I mentioned, I could generate a result set for one month with a single SELECT statement.  So, I needed to loop that SELECT statement 12 times.

    Thursday, November 11, 2010 9:47 PM