Creating a temp table with variables from a SP.

I have a stored Procedure that gives me the activity for a customer for a given time frame. It returns sperate variables for each field. ie.

Month Amount
Month2 Amount
Month3 Amount
etc,,,,,,
It does this in a 12 month increment. I would like to graph this data. I need a way to turn this into only two fields. On that contains all of the months and one that contain all of the amounts. Is there a way to do this?
jhawk3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
Could you post an example of the returned data?
one way is to define the returned data with as
i.e.
select @variable_month as month, @variable_amount as amount.
now you have 12 rows with month and amount as the heading and the data as the value.

Using:
create table #temporary_table ...
 insert into #temporary_table (month,value) values  (@variable_month, @variable_amount)  

http://www.sqlteam.com/article/temporary-tables
0
jhawk3Author Commented:
The returned data looks as follows.

Month1 = Oct '08
Month2 =Nov '08
Month3 = Dec '08

Amount1 = 15.50
Amount 2 = 156.25
Amount3 = 456.00

If I used your suggestion, which sounds like what I am looking for, how would the data be returned?
0
arnoldCommented:
You have two options to cycle through a loop to spit the data out:
Or issue sequentially
select @Month1 as month, @Amount1 as Amount
..
select @Month12 as month, @Amount12 as Amount

Or create a temporary table, and insert the data into it either via a loop or a sequential insert of the data.

Unfortunately, I do not have an example handy nor do you wish to alter the existing sp to return the data as a table.

how many variables will you have if the period you select is more then twelve months if such an option is available?  Or is the SP is always a twelve month report from an end point?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jhawk3Author Commented:
The sp is always a 12 month report. It will never have more than 12 variables.
0
arnoldCommented:
The post at:
http://bytes.com/forum/thread81368.html

Deals to some extent with your question.  A link therein points to a method to use a temp table and populate the data.
http://www.sommarskog.se/arrays-in-sql-2005.html

Is an option available to change the results to output a comma separated list for months and amounts?
0
jhawk3Author Commented:
Yes it is possible to change the results if this will allow me to do what I want.
0
arnoldCommented:
In this case, can the sp build a temporary table and return the contents rather then assembling the data in variables??
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jhawk3Author Commented:
I adjusted the SP so that the variables where inserted into a temp table, then I parsed the table for the info for the chart. Works great. Thanks for the help and the tip about temp tables.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.