SQL Server 2005 Query to pivot data

Question

I have a table set up like this

Project   Category   Date       Hours
Proj1     test       8/2/2010   2
Proj1     test       8/3/2010   8
Proj1     test       8/4/2010   4
Proj1     test       8/5/2010   3
Proj1     test       8/6/2010   5

I want to develop a query where you can input a saturday(Week Ending) date and get a result like this

WeekEnding Project Category  SunHrs MonHrs TuesHrs WedHrs ThuHrs FriHrs SatHrs  
8/7/2010   Proj1   test      0      2      8       4      3      5      0

Thank you

Answer 1

Couple of good articles to help:

http://www.devx.com/dbzone/Article/28165

http://technet.microsoft.com/en-us/library/ms177410.aspx

Answer 2

Here's one way. It looks kind of kludgey, but then pivots always look that way to me.

DECLARE @Saturday datetime

SET @Saturday = 'Aug 7, 2010'

SELECT
   @Saturday       WeekEnding
  ,Project
  ,Category
  ,isnull([1], 0)  SunHrs
  ,isnull([2], 0)  MonHrs
  ,isnull([3], 0)  TueHrs
  ,isnull([4], 0)  WedHrs
  ,isnull([5], 0)  ThuHrs
  ,isnull([6], 0)  FriHrs
  ,isnull([7], 0)  SatHrs
 from (select Project, Category, Datepart(dw, Date) DOW, Hours
        from MyTable
        --  Fixed bug from -7 to -6
        where Date between dateadd(dd, /*-7*/ -6, @Saturday) and @Saturday) Source
  pivot (max(Hours)
         for DOW in ([1],[2],[3],[4],[5],[6],[7]) ) as pvt

I used the following to set up data to test this with:

DROP TABLE MyTable
CREATE TABLE MyTable
 (
    Project    varchar(10)  not null
   ,Category   varchar(10)  not null
   ,Date       datetime     not null
   ,Hours      int          not null
 )

INSERT MyTable
 values 
('Proj1', 'test', '8/2/2010',  2 ),
('Proj1', 'test', '8/3/2010',  8 ),
('Proj1', 'test', '8/4/2010',  4 ),
('Proj1', 'test', '8/5/2010',  3 ),
('Proj1', 'test', '8/6/2010',  5 )

Answer 3

Here's basically the query you want but against a different table as it was quicker for me to do and the conversion should be more educational than just giving you a solution on a plate.

DECLARE @WeekEndingDate datetime
SET @WeekEndingDate = '20100821'

SELECT type, 
[6], [5], [4], [3], [2], [1], [0]
FROM
(
select type, DATEDIFF(day,modify_date,@WeekEndingDate) As D,create_date
 from sys.objects
where modify_date > (@WeekEndingDate-6) and 
 modify_date < (@WeekEndingDate+1)) AS SourceTable
PIVOT
(
MAX(create_date)
FOR D IN ([0], [1], [2], [3], [4], [5], [6])
) AS PivotTable