Can you help me transpose this data from rows into columns?

January 22, 2023

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	3	4	5	6	7	Total_Hours
2023	1	28.40	33.09	35.20	32.77	37.15	40.18	206.79
2023	2	29.43	19.43	36.62	34.81	38.50	41.98	200.77
2023	3	29.09	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?

>Solution :

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;