Я использую

SELECT
    Author,
    ISNULL(MAX(CASE Status WHEN 'Duplicate' THEN NumDocs END),'') AS Duplicate,
    ISNULL(MAX(CASE Status WHEN 'Failure' THEN NumDocs END),'') AS Failure,
    ISNULL(MAX(CASE Status WHEN 'Rejected' THEN NumDocs END),'') AS Rejected,
    ISNULL(MAX(CASE Status WHEN 'Success' THEN NumDocs END),'') AS Success,
    ISNULL(MAX(CASE Status WHEN 'TOTAL' THEN NumDocs END),'') AS TOTAL
FROM    
    (SELECT
        CASE WHEN (GROUPING(Author)=1) THEN 'ALL'
            ELSE ISNULL(Author,'UNKNOWN') END AS Author,
        CASE WHEN (GROUPING(Status )=1) THEN 'TOTAL'
            ELSE ISNULL(Status ,'UNKNOWN') END AS [Status],
        COUNT(Status) AS NumDocs
    FROM 
        tbl_Document D
    LEFT JOIN
        tbl_Status S
            ON
                D.status_id = S.status_id   
    GROUP BY
        Author,
        Status
    WITH ROLLUP) BASE
GROUP BY 
    Author

Преобразовывать:

[Author]  [Status]
Alan      SUCCESS
Bob       FAILURE
Bob       SUCCESS
Charles   SUCCESS
Dave      FAILURE
Dave      DUPLICATE

TO:

[Author] [SUCCESS] [FAILURE] [DUPLICATE] [TOTALS]
Alan        1         0           0         1
Bob         1         1           0         2
Charles     1         0           0         1
Dave        0         1           1         2
TOTAL       3         2           1         6

Я могу приблизиться к этому выводу, используя оператор PIVOT, но я не уверен, как получить ОБЩУЮ строку / столбец?

SELECT
  * 
FROM 
  (SELECT Author, status_id FROM tbl_Document) d
PIVOT
  (COUNT(status_id) FOR status_id IN ([1],[3],[5],[6])) p

дает:

[Author] [SUCCESS] [FAILURE] [DUPLICATE] 
Alan        1         0           0      
Bob         1         1           0      
Charles     1         0           0      
Dave        0         1           1     

Я предполагаю, что мне нужно поместить ROLLUP в подзапрос где-то ...?