I have been using
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
To transform:
[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
I can get close to this output using a PIVOT statement, but I'm not sure how to get the TOTAL row/column?
SELECT
*
FROM
(SELECT Author, status_id FROM tbl_Document) d
PIVOT
(COUNT(status_id) FOR status_id IN ([1],[3],[5],[6])) p
Gives:
[Author] [SUCCESS] [FAILURE] [DUPLICATE]
Alan 1 0 0
Bob 1 1 0
Charles 1 0 0
Dave 0 1 1
I'm guessing I need to put the ROLLUP into a subquery somewhere...?