SQL return multiple values from CASE statement

Question

How do I rewrite this SQL statement to do what I need?

 SELECT * FROM TABLE 
    WHERE COLUMN_NAME_1 IN (CASE 
        WHEN COLUMN_NAME_2 = 'X' THEN 'A' 
        WHEN COLUMN_NAME_2 = 'Y' THEN 'B', 'C' END)

Obviously I can't return multiple values from a CASE clause... so how else could I write this? I am pretty sure I am slow today because this seems so easy ....

Larry Lustig · Accepted Answer · 2011-11-08 19:45:30Z

3

 SELECT * FROM Table WHERE
    (COLUMN_NAME_2 = 'X' AND COLUMN_NAME_1 = 'A') OR
    (COLUMN_NAME_2 = 'Y' AND COLUMN_NAME_1 IN ('B', 'C'))

or

 SELECT * FROM Table WHERE COLUMN_NAME_2 = 'X' AND COLUMN_NAME_1 = 'A'
 UNION ALL
 SELECT * FROM Table WHERE COLUMN_NAME_2 = 'Y' AND COLUMN_NAME_1 IN ('B', 'C')

This presumes that you only want results with X or Y in COLUMN_NAME_2. If you want other rows it's not possible to tell which ones from your original SQL.

answered Nov 8, 2011 at 19:45

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL return multiple values from CASE statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related