SQL query with multiple/embedded CASE

Question

I am stuck in a query; if you have time can you let me know how to fix it?

I have a (hypothetical) table with TypeID, SubTypeID, Option1, Option2 and Option3 columns; last three columns are Boolean.

So, I might have something like this:

TypeID   SubTypeID       Option1       Option2       Option3
1        5               false         false         false
2        0               true          false         false
2        0               false         true          true              
2        0               true          true          true

What I am trying to get, in case TypeID=2 is the following, using example above:

'Option 1'
'Option 2, Option 3'
'Option1, Option 2, Option 3'

I tried this but getting syntax error and I am not even sure it is correct:

case when fd.TypeID=1 then ft.SubType else (case when fd.Option1=1 then 'Option 1,' else (case when fd.Option2=1 then 'Option 2,' else (case when fd.Option3=1 then 'Option 3' else '' ))) as SubType,

Answer 1

You could use IIF/CASE:

SELECT 
  IIF(Option1='true', 'Option 1,', '') +
  IIF(Option2='true', 'Option 2,', '') +
  IIF(Option3='true', 'Option 3,', '') AS Output
FROM table_name
WHERE TypeId = 2;

If you don't like final comma you could remove it with:

WITH cte AS (
SELECT 
      IIF(Option1='true', 'Option 1,', '') +
      IIF(Option2='true', 'Option 2,', '') +
      IIF(Option3='true', 'Option 3,', '') AS Output
    FROM table_name
    WHERE TypeId = 2
)
SELECT LEFT(Output, len(Output)-1)
FROM cte
WHERE Output <> '';

EDIT:

How would I enclose this inside a CASE? Something like: CASE WHEN TypeId=1 THEN something ELSE (your statements)? When I try it I get a bunch of syntax errors.

Simply:

SELECT 
  CASE WHEN Option1='true' THEN 'Option 1,' ELSE '' END +
  CASE WHEN Option2='true' THEN 'Option 2,' ELSE '' END +
  CASE WHEN Option3='true' THEN 'Option 3,' ELSE '' END AS Output
FROM table_name
WHERE TypeId = 2;