Faça uma coluna para cada contagem(*) em um dia da semana

Aqui está uma maneira. Supondo esta tabela e dados de exemplo:

USE tempdb;
GO
CREATE TABLE dbo.splunge(UserID CHAR(5), Name VARCHAR(32), Date DATE);

INSERT dbo.splunge(UserID, Name, Date) VALUES('t0001','Tod','20150629'),
  ('t0001','Tod','20150629'),('t0001','Tod','20150629'),('t0001','Tod','20150629'),
  ('t0001','Tod','20150629'),('t0001','Tod','20150628'),('t0001','Tod','20150628'),
  ('b0002','Ben','20150629'),('b0002','Ben','20150629'),('b0002','Ben','20150628');

Em seguida, com uma variável ou parâmetro para ditar a semana de seu interesse:

DECLARE @dt DATE = '20150702';

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt(dt) AS
(
  SELECT TOP (7) dt = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1, @dt)
  FROM sys.all_objects
  ORDER BY [object_id]
),
x AS 
(
  SELECT 
    s.Name, dt.dt, 
    dd = DATEDIFF(DAY, @dt, dt.dt), 
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt
  LEFT OUTER JOIN dbo.splunge AS s 
  ON s.Date = dt.dt
  GROUP BY Grouping SETS((s.Name),(s.Name,dt.dt))
)
SELECT 
  Name, 
  [S] = COALESCE(MAX(CASE dd WHEN 0 THEN c END), '-'),
  [M] = COALESCE(MAX(CASE dd WHEN 1 THEN c END), '-'),
  [T] = COALESCE(MAX(CASE dd WHEN 2 THEN c END), '-'),
  [W] = COALESCE(MAX(CASE dd WHEN 3 THEN c END), '-'),
  [T] = COALESCE(MAX(CASE dd WHEN 4 THEN c END), '-'),
  [F] = COALESCE(MAX(CASE dd WHEN 5 THEN c END), '-'),
  [S] = COALESCE(MAX(CASE dd WHEN 6 THEN c END), '-'),
  TOTAL = MAX(CASE WHEN dt IS NULL THEN c END)
FROM x
WHERE Name IS NOT NULL
GROUP BY Name;

Resultados:

Name    S   M   T   W   T   F   S   TOTAL
------  --- --- --- --- --- --- --- -----
Ben     1   2   -   -   -   -   -   3
Tod     2   5   -   -   -   -   -   7

Outra maneira:

DECLARE @dt DATE = '20150702';

-- make sure Sunday is the "beginning" of the week:
SET DATEFIRST 7;

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt AS
(
  SELECT Name, dw = DATEPART(WEEKDAY, Date)
  FROM dbo.splunge
  WHERE Date >= @dt AND Date < DATEADD(DAY, 7, @dt)
),
x AS
(
  SELECT Name, dw = COALESCE(dw, 8),  
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt 
  GROUP BY GROUPING SETS((Name),(Name,dw))
)
SELECT Name, 
  [S] = COALESCE([1],'-'),
  [M] = COALESCE([2],'-'),
  [T] = COALESCE([3],'-'),
  [W] = COALESCE([4],'-'),
  [T] = COALESCE([5],'-'),
  [F] = COALESCE([6],'-'),
  [S] = COALESCE([7],'-'),
  TOTAL = [8]
FROM x 
PIVOT (MAX(c) FOR dw IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS pvt;

Não se esqueça de limpar:

DROP TABLE dbo.splunge;

Você pode usar a seguinte abordagem no SQL Server para dinamizar os dados no formato que está procurando. Você também pode usar o operador PIVOT, mas prefiro escrever as instruções CASE, pois acho essa sintaxe mais clara (elas sempre resolveram o mesmo plano de consulta em todos os casos que verifiquei).

Resposta atualizada com base em Aaron apontando as deficiências com DATENAME. Obrigado por apontar isso, Aaron! Trabalhamos principalmente com bancos de dados baseados em inglês, mas essa foi obviamente uma suposição tola para ser feita de forma mais ampla.

-- Load test data
CREATE TABLE #reportDates (employeeName VARCHAR(100) NOT NULL, reportDate DATE NOT NULL)
GO
INSERT INTO #reportDates (employeeName, reportDate)
VALUES
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-28'),
    ('Tod', '2015-6-28'),
    ('Ben', '2015-6-29'),
    ('Ben', '2015-6-29'),
    ('Ben', '2015-6-28')

-- Build the report for each employee
DECLARE @reportStartDate DATE = '2015-6-28' /* Sunday, the start date of the report */
SELECT employeeName,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 0 THEN 1 END) AS S,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 1 THEN 1 END) AS M,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 2 THEN 1 END) AS T,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 3 THEN 1 END) AS W,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 4 THEN 1 END) AS R,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 5 THEN 1 END) AS F,
    COUNT(CASE WHEN DATEDIFF(dd, @reportStartDate, reportDate) = 6 THEN 1 END) AS S,
    COUNT(*) AS TOTAL
FROM #reportDates
WHERE reportDate BETWEEN @reportStartDate/*Sunday, report start date*/ AND DATEADD(dd, 6, @reportStartDate)/* Saturday, report end date */
GROUP BY employeeName

Para referência, aqui está a resposta original (que é incorreta se não estiver em inglês!):

-- Load test data
CREATE TABLE #reportDates (employeeName VARCHAR(100) NOT NULL, reportDate DATE NOT NULL)
GO
INSERT INTO #reportDates (employeeName, reportDate)
VALUES
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-29'),
    ('Tod', '2015-6-28'),
    ('Tod', '2015-6-28'),
    ('Ben', '2015-6-29'),
    ('Ben', '2015-6-29'),
    ('Ben', '2015-6-28')

-- Build the report for each employee
SELECT employeeName,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Sunday' THEN 1 END) AS S,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Monday' THEN 1 END) AS M,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Tuesday' THEN 1 END) AS T,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Wednesday' THEN 1 END) AS W,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Thursday' THEN 1 END) AS R,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Friday' THEN 1 END) AS F,
    COUNT(CASE WHEN DATENAME(dw, reportDate) = 'Saturday' THEN 1 END) AS S,
    COUNT(*) AS TOTAL
FROM #reportDates
WHERE reportDate BETWEEN '2015-6-28'/*Sunday, report start date*/ AND '2015-7-4'/* Saturday, report end date */
GROUP BY employeeName

Você também pode considerar o uso da DATEPARTfunção, mas saiba que ela depende de como DATEFIRSTestá configurada e, portanto, pode nem sempre retornar os mesmos resultados em bancos de dados diferentes ou se a configuração for alterada:

-- Compare DATENAME vs. DATEPART for day of the week
SELECT DATENAME(dw, '2015-6-29')
-- Monday
GO
SELECT DATEPART(dw,'2015-6-29')
-- 2
GO

-- Reconfigure the "first" day of the week to be Thursday
SET DATEFIRST 4
GO

-- Compare DATENAME vs. DATEPART for day of the week after setting DATEFIRST
SELECT DATENAME(dw,'2015-6-29')
-- Monday, which remains consistent
GO
SELECT DATEPART(dw,'2015-6-29')
-- 5, which means that DATEPART is not guaranteed for this usage!
GO

-- Reconfigure the "first" day of the week to Sunday (the default for US English)
SET DATEFIRST 7
GO

Não sei se é igual no SQL Server, fiz isso no MySQL usando a WEEKDAYfunção.

Tabela UserRecords:

Consulta:

    SELECT
        user,
        count(case when WEEKDAY(qDate)=6 THEN qDate END) as S,
        count(case when WEEKDAY(qDate)=0 THEN qDate END) as M,
        count(case when WEEKDAY(qDate)=1 THEN qDate END) as T,
        count(case when WEEKDAY(qDate)=2 THEN qDate END) as W,
        count(case when WEEKDAY(qDate)=3 THEN qDate END) as R,
        count(case when WEEKDAY(qDate)=4 THEN qDate END) as F,
        count(case when WEEKDAY(qDate)=5 THEN qDate END) as S,
        count(id) as TOTAL
    FROM UserRecords
    WHERE qDate BETWEEN '2015-06-26' AND '2015-06-29'
    GROUP BY user
    ORDER BY user DESC;

Resultado: