我想将ROLLUP与PIVOT结合使用-这是一个选择吗? [英] I would like to combine ROLLUP with PIVOT - is that an option?
问题描述
我一直在使用
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
收件人:
[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行/列?
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
赠予:
[Author] [SUCCESS] [FAILURE] [DUPLICATE]
Alan 1 0 0
Bob 1 1 0
Charles 1 0 0
Dave 0 1 1
我猜我需要将ROLLUP放入子查询中的某个地方...?
I'm guessing I need to put the ROLLUP into a subquery somewhere...?
推荐答案
您没有发布表架构,因此我尝试进行推断.我从您提供的输入开始(请参见最里面的SELECT
中的注释),因此您应该能够使其适应实际的模式.我增加了一位没有任何文档的作者,因为我认为您希望在最终报告的输出中看到那些作者.排除那些作者是微不足道的.
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
输出:
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
这篇关于我想将ROLLUP与PIVOT结合使用-这是一个选择吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!