tsql - SQL : PIVOTting Count & Percentage against a column

Question

我正在尝试生成一份报告，针对每个零件号，根据通过和失败的数量以及通过和失败的百分比显示这些零件的测试结果。

到目前为止，我有以下内容:

SELECT r2.PartNo, [Pass] AS Passed, [Fail] as Failed
    FROM
    (SELECT ResultID, PartNo, Result FROM Results) r1
PIVOT (Count(ResultID) FOR Result IN ([Pass], [Fail])) AS r2
ORDER By r2.PartNo

这是解决方案的一半(通过和失败的总数)；问题是，我该如何推进并包括百分比？

我还没有尝试过，但我想我可以从头开始，建立一系列子查询，但这更像是一种学习练习——我想知道“最好的”(最优雅的或最高效)的解决方案，所以我想我应该寻求建议。

我可以扩展这个 PIVOT 查询，还是应该采用不同的方法？

数据链接:

CREATE TABLE RESULTS (
    [ResultID] [int] NOT NULL,
    [SerialNo] [int] NOT NULL,
    [PartNo] [varchar](10) NOT NULL,
    [Result] [varchar](10) NOT NULL);

DML:

INSERT INTO Results VALUES (1, '100', 'ABC', 'Pass')
INSERT INTO Results VALUES (2, '101', 'DEF', 'Pass')
INSERT INTO Results VALUES (3, '100', 'ABC', 'Fail')
INSERT INTO Results VALUES (4, '102', 'DEF', 'Pass')
INSERT INTO Results VALUES (5, '102', 'DEF', 'Pass')
INSERT INTO Results VALUES (6, '102', 'DEF', 'Fail')
INSERT INTO Results VALUES (7, '101', 'DEF', 'Fail')

更新:

基于 bluefeet 的回答，我的解决方案是:

SELECT r2.PartNo, 
    [Pass] AS Passed, 
    [Fail] as Failed,
    ROUND(([Fail] / CAST(([Pass] + [Fail]) AS REAL)) * 100, 2) AS PercentFailed
    FROM
    (SELECT ResultID, PartNo, Result FROM Results) r1
PIVOT (Count(ResultID) FOR Result IN ([Pass], [Fail])) AS r2
ORDER By r2.PartNo

我对 FLOAT 进行了四舍五入(而不是将 CAST 转换为 DECIMAL 两次)，因为它的效率稍微高了一点，而且我还决定我们只真正需要失败 %age。

Answer 1

听起来您只需要为“通过百分比”和“失败百分比”添加一列。您可以在 PIVOT 上计算这些列。

SELECT r2.PartNo
    , [Pass] AS Passed
    , [Fail] as Failed
    , ([Pass] / Cast(([Pass] + [Fail]) as decimal(5, 2))) * 100 as PercentPassed
    , ([Fail] / Cast(([Pass] + [Fail]) as decimal(5, 2))) * 100 as PercentFailed
FROM
(
    SELECT ResultID, PartNo, Result 
    FROM Results
) r1
PIVOT 
(
    Count(ResultID) 
    FOR Result IN ([Pass], [Fail])
) AS r2
ORDER By r2.PartNo