Multiple Case Statement in SQL with aliases

Question

SELECT DISTINCT AppID,
                CASE
                  WHEN Category = '< 18 years old' THEN 'yes'
                  ELSE ''
                END AS '<18years old',
                CASE
                  WHEN Category = 'SSN Exists' THEN 'yes'
                  ELSE ''
                END AS 'Applicant has SSN',
                CASE
                  WHEN Category = 'Self Employed' THEN 'yes'
                  ELSE ''
                END AS 'Self employed'
FROM   Table1
WHERE  AppID = 123 

OUTPUT Desired I am trying to produce the results below where each AppID has only 1 line with all the information pertinent to it. The code above produces multiple lines for each application. I tried to put the case statements all together with 1 END, but I get an error when I use Aliases before the END keyword. Thanks

AppID         <18 Year old           Applicant has SSN           Self employed  

123     yes         yes
124                 yes         yes
125                 yes         yes

Answer 1

You need a group by rather than a distinct:

SELECT AppID,
       max(CASE WHEN Category = '< 18 years old' THEN 'yes'
                ELSE ''
           END) AS '<18years old',
       max(CASE WHEN Category) = 'SSN Exists' THEN 'yes'
                ELSE ''
           END) AS 'Applicant has SSN',
       max(CASE WHEN Category = 'Self Employed' THEN 'yes'
                ELSE ''
           END) AS 'Self employed'
FROM   Table1
WHERE  AppID = 123
group by AppId