SET DATEFIRST 7  --Set First Day of Week to Sunday

SELECT SUM(CASE WHEN DATEPART(weekday,[date]) = 1 THEN [column] ELSE 0 END) AS 'Sun',

 SUM(CASE WHEN DATEPART(weekday,[date]) = 2 THEN [column] ELSE 0 END) AS 'Mon',

 SUM(CASE WHEN DATEPART(weekday,[date]) = 3 THEN [column] ELSE 0 END) AS 'Tue',

 SUM(CASE WHEN DATEPART(weekday,[date]) = 4 THEN [column] ELSE 0 END) AS 'Wed',

 SUM(CASE WHEN DATEPART(weekday,[date]) = 5 THEN [column] ELSE 0 END) AS 'Thu',

 SUM(CASE WHEN DATEPART(weekday,[date]) = 6 THEN [column] ELSE 0 END) AS 'Fri',

 SUM(CASE WHEN DATEPART(weekday,[date]) = 7 THEN [column] ELSE 0 END) AS 'Sat'

FROM