I have the following data I am trying to pivot. My goal is one row for each Label, and each week becomes a column with the rate as the week's value.
| Label | Week | Rate |
|---|---|---|
| 51220 | Week 0 | -11 |
| 51220 | Week 1 | -41 |
| 51220 | Week 2 | 159 |
| 51220 | Week 3 | 117 |
| 51220 | Week 4 | 207 |
| 51220 | Week 5 | -37 |
| 51220 | Week 6 | 138 |
| 51220 | Week 7 | 139 |
| 51220 | Week 8 | -42 |
| 51220 | Week 9 | -45 |
| 51220 | Week 10 | -82 |
| 51220 | Week 11 | -85 |
| 51220 | Week 12 | -25 |
| 51347 | Week 0 | 23 |
| 51347 | Week 1 | 24 |
| 51347 | Week 2 | 25 |
| 51347 | Week 3 | 25 |
| 51347 | Week 4 | 25 |
| 51347 | Week 5 | 24 |
| 51347 | Week 6 | 24 |
| 51347 | Week 7 | 24 |
| 51347 | Week 8 | 24 |
| 51347 | Week 9 | 24 |
| 51347 | Week 10 | 24 |
| 51347 | Week 11 | 24 |
| 51347 | Week 12 | 23 |
Here my my query:
SELECT * FROM table1
PIVOT (
SUM(Rate) FOR Week IN (Week0,Week1,Week2,Week3,Week4,Week5,Week6,Week7,Week8,Week9,Week10,Week11,Week12)
) pivot_table;
This results are always NULL. What am I doing incorrectly? I'm following several tutorials with no success.
CodePudding user response：
Yeah, those brackets will do it.
SELECT *
FROM (VALUES('51220','Week 0',-11)
,('51220','Week 1',-41)
,('51347','Week 1', 24)
) table1(Label, Week, Rate)
PIVOT (SUM(rate) FOR WEEK IN ([Week 0],[Week 1])) AS pivot_table