I would like to combine ROLLUP with PIVOT - is that an option?

Question

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...?

Answer 1

You didn't post the table schema, so I tried to infer it. I started with the input you gave (see the comment in the innermost SELECT), so you should be able to adapt this to your actual schema. I included an extra author without any documents, because I figured you'd want to see those in the final report output. It's trivial to exclude those authors.

DECLARE @Status table
(
    Id int NOT NULL,
    Status nvarchar(50) NOT NULL
)

DECLARE @Authors table
(
    Id int NOT NULL,
    Name nvarchar(50) NOT NULL
)

DECLARE @Documents table
(
    Id int NOT NULL,
    AuthorId int NOT NULL,
    StatusId int NOT NULL
)

INSERT INTO @Status VALUES (1, 'Duplicate')
INSERT INTO @Status VALUES (2, 'Failure')
INSERT INTO @Status VALUES (3, 'Rejected')
INSERT INTO @Status VALUES (4, 'Success')

INSERT INTO @Authors VALUES (1, 'Alan')
INSERT INTO @Authors VALUES (2, 'Bob')
INSERT INTO @Authors VALUES (3, 'Charles')
INSERT INTO @Authors VALUES (4, 'Dave')
INSERT INTO @Authors VALUES (5, 'Tom') -- Test for authors without documents

INSERT INTO @Documents VALUES (1, 1, 4)
INSERT INTO @Documents VALUES (2, 2, 2)
INSERT INTO @Documents VALUES (3, 2, 4)
INSERT INTO @Documents VALUES (4, 3, 4)
INSERT INTO @Documents VALUES (5, 4, 2)
INSERT INTO @Documents VALUES (6, 4, 1)

SELECT
    (CASE WHEN GROUPING(Name) = 1 THEN 'Total' ELSE Name END) AS Author,
    SUM(Duplicate) AS Duplicate,
    SUM(Failure) AS Failure,
    SUM(Rejected) AS Rejected,
    SUM(Success) AS Success,
    SUM(Duplicate + Failure + Rejected + Success) AS Total
    FROM
    (
        SELECT
            Name,
            (CASE WHEN Status = 'Duplicate' THEN 1 ELSE 0 END) AS Duplicate,
            (CASE WHEN Status = 'Failure' THEN 1 ELSE 0 END) AS Failure,
            (CASE WHEN Status = 'Rejected' THEN 1 ELSE 0 END) AS Rejected,
            (CASE WHEN Status = 'Success' THEN 1 ELSE 0 END) AS Success
            FROM
            (
                -- Original input
                SELECT
                    a.Name,
                    s.Status
                    FROM @Authors a
                    LEFT OUTER JOIN @Documents d ON d.AuthorId = a.Id
                    LEFT OUTER JOIN @Status s ON d.StatusId = s.Id
            ) i
    ) j
    GROUP BY Name WITH ROLLUP

Output:

Author   Duplicate  Failure  Rejected  Success  Total
Alan     0          0        0         1        1
Bob      0          1        0         1        2
Charles  0          0        0         1        1
Dave     1          1        0         0        2
Tom      0          0        0         0        0
Total    1          2        0         3        6