PIVOT syntax in SQL Server 2005

Answer 1

Use this instead:

SELECT *
FROM TimeSheetTime
pivot 
(
    SUM(Hours)
    FOR OffSetToEntryDate
    IN ([0],[1],[2],[3],[4],[5],[6])
) as TST

The columns you want to use in your PIVOT table have to be put into square brackets [ ... ].

Getting these results:

TimeSheetTimeID TimeSheetItemID 0 1 2 3 4 5 6
1   1 1 NULL NULL NULL NULL NULL NULL
2   1 NULL 7 NULL NULL NULL NULL NULL
3   1 NULL NULL NULL 1.5 NULL NULL NULL
4   2 NULL 0 NULL NULL NULL NULL NULL
5   2 NULL NULL NULL 0 NULL NULL NULL
6   2 NULL NULL NULL NULL 0 NULL NULL
7   3 NULL 2 NULL NULL NULL NULL NULL
8   3 NULL NULL 0 NULL NULL NULL NULL
9   3 NULL NULL 4 NULL NULL NULL NULL
10  3 NULL NULL NULL NULL 1 NULL NULL
11  3 NULL NULL NULL NULL NULL 2 NULL
12  4 NULL NULL 1 NULL NULL NULL NULL
13  4 NULL NULL NULL 5 NULL NULL NULL
14  4 NULL NULL NULL NULL NULL 16 NULL
15  5 NULL NULL NULL 0.5 NULL NULL NULL
16  5 NULL NULL NULL 1 NULL NULL NULL
17  5 NULL NULL NULL NULL 1 NULL NULL
18  5 NULL NULL NULL NULL 4 NULL NULL
19  6 NULL 0.5 NULL NULL NULL NULL NULL
20  6 NULL NULL 3.2 NULL NULL NULL NULL
21  6 NULL NULL NULL NULL NULL 4 NULL
22  7 NULL 0.1 NULL NULL NULL NULL NULL
23  7 NULL NULL NULL 0.2 NULL NULL NULL
24  7 NULL NULL NULL NULL NULL 1.5 NULL
25  8 NULL 0.3 NULL NULL NULL NULL NULL
26  8 NULL NULL NULL 0.4 NULL NULL NULL
27  8 NULL NULL NULL 1 NULL NULL NULL
28  8 NULL NULL NULL NULL NULL 0.5 NULL
29  9 NULL NULL NULL 0.5 NULL NULL NULL
30  9 NULL NULL NULL NULL NULL 2 NULL

Marc

Answer 2

Ok, Figured it out. It seems that PIVOT can't be done on a number type field value. The following works well. Now I just have to figure out how to make it work within my application.

DECLARE @TimeSheetTime TABLE (
    TimeSheetItemID int NOT NULL,
    OffsetToEntryDate varchar(4) NOT NULL,
    Hours float NOT NULL
)

INSERT @TimeSheetTime (TimeSheetItemID, OffsetToEntryDate, Hours)
SELECT 1,'a1',7 
UNION SELECT 1,'a3',1.5
UNION SELECT 1,'a0',1
UNION SELECT 2,'a1',0
UNION SELECT 2,'a4',0
UNION SELECT 2,'a3',0
UNION SELECT 3,'a2',0
UNION SELECT 3,'a4',1
UNION SELECT 3,'a5',2
UNION SELECT 3,'a1',2
UNION SELECT 3,'a2',4
UNION SELECT 4,'a3',5
UNION SELECT 4,'a5',16
UNION SELECT 4,'a2',1
UNION SELECT 5,'a3',0.5
UNION SELECT 5,'a4',1
UNION SELECT 5,'a3',1
UNION SELECT 5,'a4',4
UNION SELECT 6,'a5',4
UNION SELECT 6,'a1',0.5
UNION SELECT 6,'a2',3.2
UNION SELECT 7,'a3',0.2
UNION SELECT 7,'a5',1.5
UNION SELECT 7,'a1',0.1
UNION SELECT 8,'a3',0.4
UNION SELECT 8,'a5',0.5
UNION SELECT 8,'a1',0.3
UNION SELECT 8,'a3',1
UNION SELECT 9,'a5',2
UNION SELECT 9,'a3',0.5

SELECT * FROM @TimeSheetTime
PIVOT (
    SUM(Hours)
    FOR OffsetToEntryDate
    IN (a0,a1,a2,a3,a4,a5,a6)
) AS p