Getting summary table with category based totals

I have a table containing data as illustrated below.

create table #temp(
ProjectNumber varchar(50),
ProjectName varchar(50),
ProjectCategory varchar(50),
ProjectDepartment varchar(50),
ProjectDivision varchar(50),
Hours int,
Date datetime,
Resource varchar(50)
)

Insert Into #temp Values('Project1', 'Rocket', 'ShortTerm', 'Engineering', 'Research', 5,'01-01-2007', 'John')
Insert Into #temp Values('Project1', 'Rocket', 'ShortTerm', 'Engineering', 'Research', 3,'01-02-2007', 'Mark')
Insert Into #temp Values('Project1', 'Rocket', 'ShortTerm', 'Engineering', 'Research', 8,'01-02-2007', 'John')
Insert Into #temp Values('Project1', 'Rocket', 'ShortTerm', 'Engineering', 'Research', 8,'01-02-2007', 'Mark')

Insert Into #temp Values('Project2', 'LaunchPad', 'MediumTerm', 'Constructions', 'Constructions and Infrastructure', 3,'01-01-2007', 'Mark')
Insert Into #temp Values('Project2', 'LaunchPad', 'MediumTerm', 'Constructions', 'Constructions and Infrastructure', 4,'01-02-2007', 'John')
Insert Into #temp Values('Project2', 'LaunchPad', 'MediumTerm', 'Constructions', 'Constructions and Infrastructure', 5,'01-02-2007', 'John')

Insert Into #temp Values('Project3', 'TakeOff', 'LongTerm', 'Operations', 'Research', 5,'01-01-2007', 'John')
Insert Into #temp Values('Project3', 'TakeOff', 'LongTerm', 'Operations', 'Research', 5,'01-02-2007', 'Mark')
Insert Into #temp Values('Project3', 'Takeoff', 'LongTerm', 'Operations', 'Research', 5,'01-02-2007', 'John')

I would like an output that displays :

ProjectNumber, ProjectName, ProjectCategory, ProjectDivision, Total Hours on Day 1, Total Hours on Day 2, Total Hours on Day3

And vertically at the end of each division to have a total of all the hours spent within the division like so:

Project1, Rocket, ShortTerm, Engineering, Research, 5, 19
Project3, TakeOff, LongTerm, Operations, Research, 5, 10
DivisionTotals Research, 10, 29
Project2, LaunchPad, MediumTerm, Constructions, Constructions and Infrastructure, 3, 9
DivisionTotals Constructions and Infrastructure, 3, 9

Could you enlighten me as to how I can do this?

I do know how to generate the data for the Day1, Day2, etc..columns.
And I had a pretty strong feeling I could get the vertical division totals by using a Group By Clause with Rollup. However my output continues on having multiple entries for each project.

Help?

Try:

Code Snippet

createtable #t(

ProjectNumber varchar(50),

ProjectName varchar(50),

ProjectCategory varchar(50),

ProjectDepartment varchar(50),

ProjectDivision varchar(50),

Hours int,

Date datetime,

Resource varchar(50)

)

go

InsertInto #t Values('Project1','Rocket','ShortTerm','Engineering','Research', 5,'01-01-2007','John')

InsertInto #t Values('Project1','Rocket','ShortTerm','Engineering','Research', 3,'01-02-2007','Mark')

InsertInto #t Values('Project1','Rocket','ShortTerm','Engineering','Research', 8,'01-02-2007','John')

InsertInto #t Values('Project1','Rocket','ShortTerm','Engineering','Research', 8,'01-02-2007','Mark')

InsertInto #t Values('Project2','LaunchPad','MediumTerm','Constructions','Constructions and Infrastructure', 3,'01-01-2007','Mark')

InsertInto #t Values('Project2','LaunchPad','MediumTerm','Constructions','Constructions and Infrastructure', 4,'01-02-2007','John')

InsertInto #t Values('Project2','LaunchPad','MediumTerm','Constructions','Constructions and Infrastructure', 5,'01-02-2007','John')

InsertInto #t Values('Project3','TakeOff','LongTerm','Operations','Research', 5,'01-01-2007','John')

InsertInto #t Values('Project3','TakeOff','LongTerm','Operations','Research', 5,'01-02-2007','Mark')

InsertInto #t Values('Project3','Takeoff','LongTerm','Operations','Research', 5,'01-02-2007','John')

go

select

ProjectNumber,

ProjectName,

ProjectCategory,

ProjectDivision,

sum(casewhen [Date] ='20070101'then Hours else 0 end)as'20070101',

sum(casewhen [Date] ='20070102'then Hours else 0 end)as'20070102',

sum(casewhen [Date] ='20070103'then Hours else 0 end)as'20070103'

from

#t

groupby

ProjectDivision,

ProjectNumber,

ProjectName,

ProjectCategory withrollup

having

grouping(ProjectNumber)+grouping(ProjectName)+grouping(ProjectCategory)+grouping(ProjectDivision)in(0, 3)

andgrouping(ProjectDivision)= 0

orderby

grouping(ProjectDivision),

ProjectDivision,

grouping(ProjectNumber),

ProjectNumber,

grouping(ProjectName),

ProjectName,

grouping(ProjectCategory),

ProjectCategory

go

droptable #t

go

AMB

|||
Thanks Hunchback.