I came up with 2 solutions, but I changed the pivot to a cross tabs approach. I prefer cross tabs because they give more flexibility and with more than one pivot, you get better performance.
WITH CTE AS(
SELECT CompanyNumber,
UserNumber,
Birthdate,
TestDate,
MAX(CASE WHEN TestCode = 'TestA' AND ISNULL( FailFlag, '') <> 'Fail' THEN Score END) TestA,
MAX(CASE WHEN TestCode = 'TestB' AND ISNULL( FailFlag, '') <> 'Fail' THEN Score END) TestB,
MAX(CASE WHEN TestCode = 'TestC' AND ISNULL( FailFlag, '') <> 'Fail' THEN Score END) TestC,
MAX(CASE WHEN TestCode = 'TestD' THEN Score END) TestD
FROM testtable
GROUP BY CompanyNumber,
UserNumber,
Birthdate,
TestDate
)
SELECT *
FROM CTE
WHERE TestA IS NOT NULL
AND TestB IS NOT NULL
AND TestC IS NOT NULL
SELECT CompanyNumber,
UserNumber,
Birthdate,
TestDate,
MAX(CASE WHEN TestCode = 'TestA' THEN Score END) TestA,
MAX(CASE WHEN TestCode = 'TestB' THEN Score END) TestB,
MAX(CASE WHEN TestCode = 'TestC' THEN Score END) TestC,
MAX(CASE WHEN TestCode = 'TestD' THEN Score END) TestD
FROM testtable t
WHERE NOT EXISTS( SELECT *
FROM testtable x
WHERE x.CompanyNumber = t.CompanyNumber
AND x.UserNumber = t.UserNumber
AND x.TestDate = t.TestDate
AND (x.FailFlag = 'Fail'AND x.TestCode <> 'TestD'))
GROUP BY CompanyNumber,
UserNumber,
Birthdate,
TestDate