Я хотел бы объединить ROLLUP с PIVOT - это вариант?

я использовал

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, но я не уверен, как получить строку/столбец TOTAL?

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 в подзапрос где-нибудь...?


sql sql-server rollup pivot
person Alan Dixey    schedule 10.09.2010    source источник

Ответы (1)


arrow_upward
4
arrow_downward

Вы не опубликовали схему таблицы, поэтому я попытался сделать вывод. Я начал с ввода, который вы дали (см. комментарий в самом внутреннем 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
person Jon Seigel    schedule 13.11.2010