That will split
100 0 John
100 1 Jill
into 2 separate rows I think, rather than combining them?
I think it will need some sort of ROW_NUMBER() OVER() to get the first row, and then the Application0, ... Y/N columns from an Aggregate
Or just OUTER JOIN the table 2x additional times to get Applicant1 and Applicant2
SELECT AccountNumber, ApplicantName
, CASE WHEN A0.AccountNumber IS NOT NULL THEN 'Y' ELSE 'N' END AS 'Applicant0'
, CASE WHEN A1.AccountNumber IS NOT NULL THEN 'Y' ELSE 'N' END AS 'Applicant1'
, CASE WHEN A2.AccountNumber IS NOT NULL THEN 'Y' ELSE 'N' END AS 'Applicant2'
from tablename AS A
LEFT OUTER JOIN tablename AS A0
ON A0.AccountNumber = A.AccountNumber
AND A0.ApplicantId = 0
LEFT OUTER JOIN tablename AS A1
ON A1.AccountNumber = A.AccountNumber
AND A1.ApplicantId = 1
LEFT OUTER JOIN tablename AS A2
ON A2.AccountNumber = A.AccountNumber
AND A2.ApplicantId = 2
WHERE ApplicantId =0
That won't work if it is possible to have an AccountNumber where the first applicant is NOT ApplicantId =0, instead it will have to select the "lowest" ApplicantId for each AccountNumber