each account can have applicant 1, 2, 3 people on the account (there is always 1 person, both 2, 3 are optional).
Question:
In the out put I need to list only applicant 1, but indicate a flag if there is applicant 1 or 2 present on this account.
( in the above example account 100 has 2 applicants, 101 has just 1, 102 has 3)
account number # applicant1 name #, applicant2 present#(Y/N) applicant3 present# (Y/N)
DECLARE @tAccount TABLE
( Account INT,
Person TINYINT,
PersonName VARCHAR(30))
INSERT INTO @tAccount(Account,Person,PersonName)
VALUES (100,1,'John'),(100, 2, 'Jill'),
(101, 1, 'Josh'),
(102, 1, 'Jack'),(102, 2, 'Jarred'),(102, 3, 'Johana')
SELECT
Account
,MAX(CASE WHEN Person = 1 THEN PersonName ELSE '' END) AS PersonName
,MAX([applicant2 present]) AS [applicant2 present]
,MAX([applicant3 present]) AS [applicant3 present]
FROM
(
SELECT
Account, PersonName , Person
,CASE WHEN Person = 2 THEN 'Y' ELSE 'N' END AS [applicant2 present]
,CASE WHEN Person = 3 THEN 'Y' ELSE 'N' END AS [applicant3 present]
FROM
@tAccount
)A
GROUP BY Account
SELECT
Account,PersonName
,[applicant2 present]
,[applicant3 present]
FROM
@tAccount AS A1
OUTER APPLY
( SELECT
MAX(CASE WHEN Person = 2 THEN 'Y' ELSE 'N' END) AS [applicant2 present]
,MAX(CASE WHEN Person = 3 THEN 'Y' ELSE 'N' END) AS [applicant3 present]
FROM
@tAccount AS A2
WHERE
A2.Account = A1.Account
)A3
WHERE
Person = 1
Account PersonName applicant2 present applicant3 present
100 John Y N
101 Josh N N
102 Jack Y Y
SELECT
Account
,PersonName AS [applicant1 name]
,CASE WHEN NoApplicants >= 2 THEN 'Y' ELSE 'N' END AS [applicant2 present]
,CASE WHEN NoApplicants >= 3 THEN 'Y' ELSE 'N' END AS [applicant3 present]
FROM
(
SELECT
Account,PersonName,Person
,COUNT(Account) OVER(PARTITION BY Account) AS NoApplicants
FROM
@tAccount
)A
WHERE
A.Person = 1