multiple GROUP BY columns into one column

Question

Products are grouped for inspected and pass/fail on about 20 criteria. They want a report that counts how many of each defect an individual group has.

Defect* is varchar(3) and is used to identify which criteria failed.
The table has 3 columns for defects and I can return them with something like:

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

Which gives output like:

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
...

Desired output:

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

on SQL Server 7, yes I know.

Answer 1

You could use a View to simulate a 1NF table then it would be straightforward.

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

Answer 2

For SQL Server 2005/2008 use UNPIVOT function.
I didn't tested, so consider as pseudocode:
SELECT GroupID, DefectType, COUNT(*) AS Occurrences
From
--building the table
--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