Select Statement problem

Hey,Im using SQL Server 2005 and im using stored procedures. Is this possible? i need to get a number of results and do it in one SELECT statement or just one result set. Im using MS Chart Controls for reporting and they cant seem to process multiple result sets this is my problem, so basically to run a barchart i need to assign a column to the X axis and a column to the Y axis and it will then display my results.

My stored procedure below returns a result set with columns subpolicy_name, building_name, floor_name, room_name and total_savings. Im just wondering is it possible to split my total_savings column into 4 columns, for example subpolicy_savings, building_savings, floor_savings and room_savings, i need everything in a separate column. If you run this stored procedure you will see the result set.

Thanks in advance for any help

ALTER PROCEDURE dbo.TEST_SAMPLE_DATA

AS

CREATE TABLE #CO2temp

(
pc_profile_id int,
shutdown_Time datetime NULL,
hibernate_Time datetime NULL,
sleep_Time datetime NULL,
startup_Time datetime NULL,
status varchar(50),
subpolicy_name varchar(50),
building_name varchar(50),
floor_name varchar(50),
room_name varchar(50),
total_hrs_off int,
pc_kwh_rate float,
CO2_factor float,
CO2_savings float,
)

insert #CO2temp (pc_profile_id, shutdown_Time, startup_Time, subpolicy_name, building_name, floor_name, room_name, pc_kwh_rate, CO2_factor)

SELECT '2', '04/09/2009 11:02:08' , '04/09/2009 16:03:03', 'Exempt', 'Kane', 'floor1', 'Room1.1', '1.2','3.9' UNION ALL

SELECT '3', '04/09/2009 11:00:17','04/09/2009 16:03:00', 'Exempt','Kane', 'floor2', 'Room1.2', '1.2', '3.9' UNION ALL

SELECT '4', '04/09/2009 14:52:17','04/09/2009 16:02:57', 'Sales', 'Kane', 'floor4', 'Room2.0', '1.2', '3.9' UNION ALL

SELECT '5', '04/09/2009 12:12:10','04/09/2009 16:12:50', 'Test', 'Building B', 'floor2', 'Room2.0', '1.2', '3.9' UNION ALL

SELECT '6', '04/09/2009 12:12:10', '04/09/2009 18:12:50', 'Finance', 'Building C', 'floor3', 'Room4.1', '1.2','3.9' UNION ALL

SELECT '7', '04/09/2009 14:12:10', '04/09/2009 18:12:50', 'IT', 'Building d', 'floor7', 'Room4.6', '1.2','3.9' UNION ALL

SELECT '8', '04/09/2009 15:12:10', '04/09/2009 18:12:50', 'Manage', 'Building e', 'floor4', 'Room4.7', '1.2','3.9' UNION ALL

SELECT '9', '04/09/2009 13:12:10', '04/09/2009 18:12:50', 'Finance', 'Building C', 'floor6', 'Room4.1', '1.2','3.9'

UPDATE #CO2temp
SET total_hrs_off = DATEDIFF(HOUR,
COALESCE(shutdown_Time,startup_Time),
COALESCE(startup_Time,shutdown_Time))

UPDATE #CO2temp
SET CO2_savings = (isnull(total_hrs_off, 0) * pc_kwh_rate * CO2_factor)

/**------------------------------------------------------------------*/

/** Getting Total Savings per Policy*/
Select 'Policy' AS Sector,
subpolicy_name,
'' AS building_name,
'' AS floor_name,
'' AS room_name,
SUM(CO2_savings) As total_savings
From #CO2temp
group by subpolicy_name
UNION ALL

/** Getting Total Savings per Building*/
Select 'Building' AS Sector,
'' AS subpolicy_name,
building_name,
'' AS floor_name,
'' AS room_name,
SUM(CO2_savings) As total_savings
From #CO2temp
group by building_name
UNION ALL
/** Getting Total Savings per Floor*/
Select 'Floor' AS Sector,
'' AS subpolicy_name,
'' AS building_name,
floor_name,
'' AS room_name,
SUM(CO2_savings) As total_savings
From #CO2temp
group by floor_name
UNION ALL

/** Getting Total Savings per Room*/
Select 'Room' AS Sector,
'' AS subpolicy_name,
'' AS building_name,
'' AS floor_name,
room_name,
SUM(CO2_savings) As total_savings
From #CO2temp
group by room_name


RETURN

Frank,

Appreciate the reply, Im not sure if its possible what Im trying to do. Unfortunately Im using MS Chart Controls which cant seem to process multiple result sets. So for example for a bar chart i need to assign a column to the X axis and a column to the Y Axis. I need to group my savings by:

Subpolicy
Building
Floor
Room

So for example if i point to the subpolicy_name as the X axis and then the subpolicy_savings as the Y axis, my chart will pull everything in the subpolicy_savings including the 0's. My output i guess needs top be something like this but in one select statement. I've been trying to do this for a few days now, Im not sure if its possible to be honest, thanks again for the help

/** Getting Total Savings per Policy*/
Select subpolicy_name, SUM(CO2_savings) As total_savings
From #CO2temp
group by subpolicy_name
Order by subpolicy_name

/** Getting Total Savings per Building*/
Select building_name, SUM(CO2_savings) As total_savings
From #CO2temp
group by building_name
Order by building_name

/** Getting Total Savings per Floor*/
Select floor_name, SUM(CO2_savings) As total_savings
From #CO2temp
group by floor_name
Order by floor_name

/** Getting Total Savings per Room*/
Select room_name, SUM(CO2_savings) As total_savings
From #CO2temp
group by room_name
Order by room_name

RETURN

Frank Thanks for the reply, I've been trying to solve this for 4 days now and cant get the result i need. unfortunely im stuck using MS Chart Controls and they cant process multiple result sets. Thats why i need everything in a separate column so when the bar chart runs the X axis will b assigned to one column in my Stored procedure and the Y axis to another, it then returns all the records in these columns.I think im trying to the impossible thanks for the help anyway i appreciate it.