将多个GROUP BY列合并为一列

Question

产品按照大约20个标准进行分组检查，通过/不通过。他们需要一份报告，计算每个单独的组有多少个缺陷。

Defect*是varchar(3)类型，用于识别哪些标准未通过。
表格有3列缺陷信息，可以使用以下方式返回它们：

SELECT GroupID,
    Defect1, COUNT(Defect1) as Occ1,
    Defect2, COUNT(Defect2) as Occ2,
    Defect3, COUNT(Defect3) as Occ3
FROM Product
WHERE Run = 1728 AND Defect1 IS NOT NULL
GROUP BY GroupID, Defect1, Defect2, Defect3
ORDER BY GroupID

即可输出如下结果：

GroupID Def1    Occ1    Def2    Occ2    Def3    Occ3
RF-061   CPP       1     FPV       1    null       0
RF-061   FPV       1     CPP       1    null       0
RF-061   HCR       1     CHP       1    null       0
RF-061   CHP       1     FPV       1    null       0
RF-061   FBL       1     HCR       1     FPT       1
RF-061   CHP       1     CPP       1     HCR       1
RF-061   CHP       1     CPP       1    null       0
RF-061   CPP       1     FBL       1    null       0
...

期望输出：

GrPupID Def Occurrences
BF-061  FPV 4
BF-061  CPP 5
BF-061  CHP 5
BF-061  HCR 5
BF-061  FBL 3
BF-061  PPC 1
BF-061  FPT 1

在SQL Server 7上，是的我知道。

- 182764125216

Answer 1

您可以使用视图来模拟一个1NF表，这样就会变得容易明了。

CREATE VIEW tempView
AS
SELECT GroupID, Defect1 AS Defect, Run 
FROM Product
UNION ALL
SELECT GroupID, Defect2 AS Defect, Run 
FROM Product
UNION ALL
SELECT GroupID, Defect3 AS Defect, Run 
FROM Product

GO

SELECT GroupID, Defect, COUNT(*) AS Occurrences
FROM tempView
WHERE Run = 1728 
GROUP BY GroupID, Defect
ORDER BY GroupID

- Martin Smith

Answer 2

对于 SQL Server 2005/2008，请使用 UNPIVOT 函数。
我没有测试过，所以请将其视为伪代码：
SELECT GroupID、DefectType、COUNT(*) AS Occurrences
From
--构建表格
--GroupID Defect DefectType
--RF-061 Defect1 CPP
--RF-061 Defect1 FPV
--.............
(
SELECT GroupID、Defect、DefectType
FROM
(SELECT GroupID、Defect1、Defect2、Defect3
FROM Product) p
UNPIVOT
(DefectType FOR Defect IN ( Defect1、Defect2、Defect3 )
)AS unpvt
) x

- dragos55

---

非常抱歉犯了错误，在这种情况下，UNION是您的好帮手。 - dragos55