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

score 1 · Answer 1 · answered Nov 29 '12 at 16:45

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

answered Nov 29 '12 at 16:45

Gordon Linoff

1,242,037
58
646
786

Thanks for your reply. Why would I use a MAX function if I do not look for a maximum value ? Is the MAX function - will help all values appear on the same line ? – user1860212 Nov 29 '12 at 19:37

score 0 · Answer 2 · answered Nov 29 '12 at 16:42

You can nest the Case statements as follows and just alias at the end:

CASE WHEN 
    Category = '< 18 years old' 
THEN 'yes'
ELSE
    CASE WHEN
        Category = 'SSN Exists'
    THEN 'yes'
    ELSE
        ...
        ...
    END
END AS Alias

Is that what you're after?

score 0 · Answer 3 · answered Nov 29 '12 at 16:43

0

Your aliases should not be enclosed in ''.

Check on your DB Engine which type of character you must use to enclose aliases.

answered Nov 29 '12 at 16:43

xlecoustillier

16,183
14
60
85

score 0 · Answer 4 · answered Nov 29 '12 at 16:44

If I understand your problem correctly, you need to do a GROUP BY:

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

Thanks for your reply. I also have to count how many occurences in — user1860212, Nov 29 '12 at 20:19

Multiple Case Statement in SQL with aliases

4 Answers4