data manipulation group by problem

Hi I have a data manipulation query. I have the table below which gets
populated on a daily basis
CREATE TABLE [dbo].[FreeSpace] (
[Drive] [char] (1) not null ,
[MB_Free] [int] not null ,
[day_time] [datetime] default getdate()NOT NULL
) ON [PRIMARY]
insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
populates table
I run the query included below but the data comes out as example below
drive monday tuesday etc....
c 100mb null
c null 100mb
d 200mb null
d null 200mb
I would like to display like this
drive monday tuesday etc....
c 100mb 100mb
D 200mb 200mb
My query below
SELECT Drive,
case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Monday,
case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Tuesday,
case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Wednesday,
case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Thursday,
case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Friday,
case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Saturday,
case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Sunday
from FreeSpace
group by Drive,datepart(dd, day_time),MB_Free
order by drive
thanks for any help
SammySammy
See if this helps you
SELECT Drive,
MAX(case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +'
MB
Free Space'
end) as Monday,
MAX(case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +'
MB
Free Space'
end )as Tuesday
......
from FreeSpace
group by Drive
order by drive
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:E4C10338-8829-42B6-A3B9-F87B1224EFF9@.microsoft.com...
> Hi I have a data manipulation query. I have the table below which gets
> populated on a daily basis
> CREATE TABLE [dbo].[FreeSpace] (
> [Drive] [char] (1) not null ,
> [MB_Free] [int] not null ,
> [day_time] [datetime] default getdate()NOT NULL
> ) ON [PRIMARY]
> insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
> populates table
> I run the query included below but the data comes out as example below
> drive monday tuesday etc....
> c 100mb null
> c null 100mb
> d 200mb null
> d null 200mb
> I would like to display like this
> drive monday tuesday etc....
> c 100mb 100mb
> D 200mb 200mb
> My query below
> SELECT Drive,
> case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Monday,
> case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Tuesday,
> case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Wednesday,
> case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Thursday,
> case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Friday,
> case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Saturday,
> case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Sunday
> from FreeSpace
> group by Drive,datepart(dd, day_time),MB_Free
> order by drive
> thanks for any help
> Sammy|||Thanks Uri
"Sammy" wrote:

> Hi I have a data manipulation query. I have the table below which gets
> populated on a daily basis
> CREATE TABLE [dbo].[FreeSpace] (
> [Drive] [char] (1) not null ,
> [MB_Free] [int] not null ,
> [day_time] [datetime] default getdate()NOT NULL
> ) ON [PRIMARY]
> insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
> populates table
> I run the query included below but the data comes out as example below
> drive monday tuesday etc....
> c 100mb null
> c null 100mb
> d 200mb null
> d null 200mb
> I would like to display like this
> drive monday tuesday etc....
> c 100mb 100mb
> D 200mb 200mb
> My query below
> SELECT Drive,
> case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Monday,
> case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Tuesday,
> case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Wednesday,
> case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Thursday,
> case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Friday,
> case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Saturday,
> case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Sunday
> from FreeSpace
> group by Drive,datepart(dd, day_time),MB_Free
> order by drive
> thanks for any help
> Sammy