SELECT AccountNumber,
, max(case when ApplicantId=0 then ApplicantName end) 'ApplicantName'
, isnull(max(CASE WHEN ApplicantId =0 THEN 'Y' END),'N') 'Applicant0'
, isnull(max(CASE WHEN ApplicantId =1 THEN 'Y' END),'N') 'Applicant1'
, isnull(max(CASE WHEN ApplicantId =2 THEN 'Y' END),'N') 'Applicant2'
from tablename
GROUP BY AccountNumber
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
SELECT
AccountNumber,
MAX(CASE WHEN row_num = 0 THEN ApplicantName END) AS Applicant0_Name,
'Y' AS Applicant0,
MAX(CASE WHEN row_num = 1 THEN 'Y' ELSE 'N' END) AS Applicant1,
MAX(CASE WHEN row_num = 2 THEN 'Y' ELSE 'N' END) AS Applicant2
FROM (
SELECT AccountNumber, ApplicantId, ApplicantName,
ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY ApplicantId) - 1 AS row_num
FROM tablename
) AS derived
WHERE row_num BETWEEN 0 AND 2
GROUP BY AccountNumber
ORDER BY AccountNumber