Ich möchte ROLLUP mit PIVOT kombinieren - ist das eine Option?
Ich habe @ verwend
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
Transformieren
[Author] [Status]
Alan SUCCESS
Bob FAILURE
Bob SUCCESS
Charles SUCCESS
Dave FAILURE
Dave DUPLICATE
ZU
[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
Ich kann mit einer PIVOT-Anweisung an diese Ausgabe herangehen, bin mir aber nicht sicher, wie ich die TOTAL-Zeile / -Spalte abrufen soll?
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
Ich schätze, ich muss den ROLLUP in eine Unterabfrage einfügen ...?