Crosstab Query

I have two tables Bill and Location.
Bill
(
location_id int,
prod_period datetime,
consumption float,
demand float
)

Location
(
location_id int,
location_name varchar(45)
)

I want to create a stored procedure that takes a parameter of @.year. I
basically want the procedure to return results that show locations
where consumption and/or demand IS NULL or 0 for each month throughout
a given year. I would like my results to look something like this:

location_id year Jan Feb Mar Apr May Jun Jul Aug Sept Oct
Nov Dec
1 2005 0 0 0
2 2005 0 0 0 0
3 2005 0 0

If data does exist for consumption or demand, I would like to show it,
however I am really interested in the locations that have no data
associated with them.

Any ideas of how I can accomplish this?Hi

There are many posts on Crosstab queries, usually the fastest solution is to
use the client side tools to produce this. You may also want to check out
http://www.windowsitpro.com/SQLServ...5608/15608.html.

John

<burke.david@.gmail.com> wrote in message
news:1126090825.869267.236710@.g47g2000cwa.googlegr oups.com...
>I have two tables Bill and Location.
> Bill
> (
> location_id int,
> prod_period datetime,
> consumption float,
> demand float
> )
> Location
> (
> location_id int,
> location_name varchar(45)
> )
> I want to create a stored procedure that takes a parameter of @.year. I
> basically want the procedure to return results that show locations
> where consumption and/or demand IS NULL or 0 for each month throughout
> a given year. I would like my results to look something like this:
> location_id year Jan Feb Mar Apr May Jun Jul Aug Sept Oct
> Nov Dec
> 1 2005 0 0 0
> 2 2005 0 0 0 0
> 3 2005 0 0
> If data does exist for consumption or demand, I would like to show it,
> however I am really interested in the locations that have no data
> associated with them.
> Any ideas of how I can accomplish this?