Combing multiple result sets from SELECT

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

Answer 1

try :

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

 

---

Best regards

Answer 2

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

Answer 3

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

Answer 4

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.