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?
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?
ASKER
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?
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?
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?
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?
ASKER
The sp is always a 12 month report. It will never have more than 12 variables.
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?
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?
ASKER
Yes it is possible to change the results if this will allow me to do what I want.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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