Looks like you found the sad & pathetic limitations of using the pivot operator... The "old school" cross tab method handles this sort of thing without batting an eye...
Your sample code didn't have an identity column so I just used WorkId instead... Swapping out to a different column should be easy enough though...
IF OBJECT_ID('tempdb..#Table1', 'U') IS NOT NULL
BEGIN DROP TABLE #Table1; END;
CREATE TABLE #Table1 (TaskId int, TaskName varchar(200));
INSERT #Table1 (TaskId, TaskName) VALUES (1,'Test Task'), (2,'Test Task1'), (3,'Test Task2');
IF OBJECT_ID('tempdb..#Table2', 'U') IS NOT NULL
BEGIN DROP TABLE #Table2; END;
CREATE TABLE #Table2 (WorkId int, TaskId int, Workday datetime, WorkHours decimal(18,2));
INSERT #Table2 (WorkId,TaskId, Workday,WorkHours) VALUES
(1,1,'2018-03-04',4),
(1,2,'2018-03-04',4),
(2,1,'2018-03-05',2),
(2,2,'2018-03-05',3),
(2,3,'2018-03-05',3),
(3,2,'2018-03-06',8),
(4,2,'2018-03-07',8),
(5,2,'2018-03-08',8),
(6,2,'2018-03-09',8);
--=======================================================================================
DECLARE
@startdate DATETIME = '2018-03-04',
@debug BIT = 0;
DECLARE
@cols NVARCHAR(4000) = N'',
@sql NVARCHAR(4000) = N''
SELECT
@cols = CONCAT(@cols, CHAR(13), CHAR(10), CHAR(9), N'[', dx.day_name, N' ', dx.day_of_month, N'] = ISNULL(NULLIF(CONCAT(SUM(CASE WHEN dw.day_of_week = ',
dx.day_of_week, N' THEN t2.WorkHours ELSE NULL END), '' '', MIN(CASE WHEN dw.day_of_week = ',
dx.day_of_week, N' THEN t2.WorkId ELSE NULL END), '' - '', MAX(CASE WHEN dw.day_of_week = ',
dx.day_of_week, N' THEN t2.WorkId ELSE NULL END)), '' - ''), ''''),')
--*
FROM
( VALUES (0),(1),(2),(3),(4),(5),(6) ) dta (days_to_add)
CROSS APPLY ( VALUES ( DATEADD(DAY, dta.days_to_add, @startdate)) ) dv (date_val)
CROSS APPLY ( VALUES (DATEPART(WEEKDAY, dv.date_val), UPPER(LEFT(DATENAME(WEEKDAY, dv.date_val), 3)), DATEPART(DAY, dv.date_val)) ) dx (day_of_week, day_name, day_of_month)
--PRINT(@cols)
SET @sql = CONCAT(N'
SELECT
t1.TaskName,',
@cols, N'
[TOTAL] = ISNULL(NULLIF(CONCAT(SUM(t2.WorkHours), '' '', MIN(t2.WorkId), '' - '', MAX(t2.WorkId)), '' - ''), '''')
FROM
#Table1 t1
JOIN #Table2 t2
ON t1.TaskId = t2.TaskId
CROSS APPLY ( VALUES (DATEPART(WEEKDAY, t2.Workday)) ) dw (day_of_week)
WHERE
t2.Workday >= @_startdate
AND t2.Workday < DATEADD(DAY, 7, @_startdate)
GROUP BY
t1.TaskId,
t1.TaskName
ORDER BY
t1.TaskId;');
IF @debug = 1
BEGIN
PRINT(@sql);
END;
ELSE
BEGIN
EXEC sys.sp_executesql @sql, N'@_startdate DATETIME', @_startdate = @startdate;
END;
Hope this helps,
Jason