SQLTeam.com | Weblogs | Forums

SQL complex query help


#1

I have a table with the following data.
AccountNumber ApplicantId ApplicantName
100 0 John
100 1 Jill
101 0 Josh
102 0 Jack
102 1 Jarred
102 2 Johana

Need output in the following format. What SQL query I can use. Thanks

AccountNumber Applicant0_Name Applicant0 Applicant1 Applicant2
100 John Y Y N
101 Josh Y N N
102 Jack Y Y Y


#2

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


#3

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


#4

This is the closet answer so far, however each row is repeating #of times the total rows in the table.


#5
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

#6

AccountNumber / ApplicantId column combination is not unique within the table?