SQL Server 2005 PIVOT Syntax
Pulling my hair out with this question. Maybe some of the experts can see what I am doing wrong?
I have a TimeSheetTime table like this:
CREATE TABLE TimeSheetTime(
TimeSheetTimeID int IDENTITY(1,1) NOT NULL,
TimeSheetItemID int NOT NULL,
OffsetToEntryDate tinyint NOT NULL,
Hours float NOT NULL
)
This is populated with some data like this:
INSERT TimeSheetTime (TimeSheetItemID, OffsetToEntryDate, Hours)
SELECT 1,1,7
UNION SELECT 1,3,1.5
UNION SELECT 1,0,1
UNION SELECT 2,1,0
UNION SELECT 2,4,0
UNION SELECT 2,3,0
UNION SELECT 3,2,0
UNION SELECT 3,4,1
UNION SELECT 3,5,2
UNION SELECT 3,1,2
UNION SELECT 3,2,4
UNION SELECT 4,3,5
UNION SELECT 4,5,16
UNION SELECT 4,2,1
UNION SELECT 5,3,0.5
UNION SELECT 5,4,1
UNION SELECT 5,3,1
UNION SELECT 5,4,4
UNION SELECT 6,5,4
UNION SELECT 6,1,0.5
UNION SELECT 6,2,3.2
UNION SELECT 7,3,0.2
UNION SELECT 7,5,1.5
UNION SELECT 7,1,0.1
UNION SELECT 8,3,0.4
UNION SELECT 8,5,0.5
UNION SELECT 8,1,0.3
UNION SELECT 8,3,1
UNION SELECT 9,5,2
UNION SELECT 9,3,0.5
Now, I would like to return a dataset that has a TimeSheetItemID and then a PIVOT SUM of hours located in the OffsetToEntryDate columns. ie:
TimeSheetItemID | 0 | 1 | 2 | 3 | 4 | 5 | 6
I tried the following
SELECT *
FROM TimeSheetTime
pivot
(
SUM(Hours)
FOR OffSetToEntryDate
IN (0,1,2,3,4,5,6)
) as TST
Any suggestions are greatly appreciated.
+2
source to share
2 answers
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 the PIVOT table must be enclosed in 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
Mark
+4
source to share
Okay, got it. It looks like PIVOT cannot be done on a number type field value. The following works well. Now I just need to figure out how to make it work in 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
0
source to share