clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805483 fiddles created (40815 in the last week).

CREATE TABLE MyTable ( [Date] date, [Day] varchar(3), [Values] int NULL ) INSERT INTO MyTable([Date], [Day], [Values]) VALUES('2020-08-30', 'Sun', NULL), ('2020-08-31', 'Mon', NULL), ('2020-09-01', 'Tue', 1), ('2020-09-02', 'Wed', 1), ('2020-09-03', 'Thu', 1), ('2020-09-04', 'Fri', 1), ('2020-09-05', 'Sat', NULL), ('2020-09-06', 'Sun', NULL), ('2020-09-07', 'Mon', 9), ('2020-09-08', 'Tue', 9), ('2020-09-09', 'Wed', 9), ('2020-09-10', 'Thu', 9), ('2020-09-11', 'Fri', 9), ('2020-09-12', 'Sat', NULL)
14 rows affected
 hidden batch(es)


SELECT WeekOfYear, [Sun] = MAX(CASE WHEN DayOfWeek=1 THEN NewVal ELSE NULL END), [Mon] = MAX(CASE WHEN DayOfWeek=2 THEN NewVal ELSE NULL END), [Tue] = MAX(CASE WHEN DayOfWeek=3 THEN NewVal ELSE NULL END), [Wed] = MAX(CASE WHEN DayOfWeek=4 THEN NewVal ELSE NULL END), [Thu] = MAX(CASE WHEN DayOfWeek=5 THEN NewVal ELSE NULL END), [Fri] = MAX(CASE WHEN DayOfWeek=6 THEN NewVal ELSE NULL END), [Sat] = MAX(CASE WHEN DayOfWeek=7 THEN NewVal ELSE NULL END) FROM ( SELECT DATEPART(DW, [Date]) DayOfWeek, DATEPART(WK, [Date]) WeekOfYear, [Day], CONCAT([Date], '->', COALESCE([Values], 0)) AS [NewVal] FROM MyTable ) T GROUP BY WeekOfYear
WeekOfYear Sun Mon Tue Wed Thu Fri Sat
36 2020-08-30->0 2020-08-31->0 2020-09-01->1 2020-09-02->1 2020-09-03->1 2020-09-04->1 2020-09-05->0
37 2020-09-06->0 2020-09-07->9 2020-09-08->9 2020-09-09->9 2020-09-10->9 2020-09-11->9 2020-09-12->0
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)