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

Question

I used

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

Convert:

[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 closer to this conclusion with the 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 guess I need to put ROLLUP in a subquery somewhere ...?

Answer 1

, . , (. SELECT), . - , , . .

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

:

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