Sample Input:
| YearNum | WeekNum | DayNum | Hours |
|---|---|---|---|
| 2023 | 1 | 1 | 28.40 |
| 2023 | 1 | 3 | 33.09 |
| 2023 | 1 | 4 | 35.20 |
| 2023 | 1 | 5 | 32.77 |
| 2023 | 1 | 6 | 37.15 |
| 2023 | 1 | 7 | 40.18 |
| 2023 | 2 | 1 | 29.43 |
| 2023 | 2 | 3 | 19.43 |
| 2023 | 2 | 4 | 36.62 |
| 2023 | 2 | 5 | 34.81 |
| 2023 | 2 | 6 | 38.50 |
| 2023 | 2 | 7 | 41.98 |
| 2023 | 3 | 1 | 29.09 |
| 2023 | 3 | 3 | 28.63 |
| 2023 | 3 | 4 | 41.59 |
I'm attempting to write sql to transpose the records in the sample input table to obtain the final product as shown in the following output.
Expected Output:
| YearNum | WeekNum | 1 | 2 | 3 | 4 | 5 | 6 | 7 | Total_Hours |
|---|---|---|---|---|---|---|---|---|---|
| 2023 | 1 | 28.40 | 0.0 | 33.09 | 35.20 | 32.77 | 37.15 | 40.18 | 206.79 |
| 2023 | 2 | 29.43 | 0.0 | 19.43 | 36.62 | 34.81 | 38.50 | 41.98 | 200.77 |
| 2023 | 3 | 29.09 | 0.0 | 28.63 | 41.59 | 0.0 | 0.0 | 0.0 | 99.31 |
Notice that the OUTPUT has all 7 days (1,2,3,4,5,6,7) even when the INPUT doesn't have that info.
How would I do this?
CodePudding user response：
Can you try out the following script and see if it will be of help?
SELECT
YearNum, WeekNum,
MAX(CASE WHEN DayNum = 1 THEN Hours END) AS [1],
MAX(CASE WHEN DayNum = 2 THEN Hours END) AS [2],
MAX(CASE WHEN DayNum = 3 THEN Hours END) AS [3],
MAX(CASE WHEN DayNum = 4 THEN Hours END) AS [4],
MAX(CASE WHEN DayNum = 5 THEN Hours END) AS [5],
MAX(CASE WHEN DayNum = 6 THEN Hours END) AS [6],
MAX(CASE WHEN DayNum = 7 THEN Hours END) AS [7],
SUM(Hours) AS Total_Hours
FROM INPUT
GROUP BY YearNum, WeekNum;