Stringing together a CASE expression

[jahil]

I am converting some MS Access queries into sharepointe reporting services and I'm stuck.

I have a query in access that basically says:

IIF ([column1] = true, 'yes1, ') & IIF ([column2] = true, 'yes2, ') as Results
 

Below are the results I would get.

 UserID   Results
User1    Yes1, 
User2    Yes1, Yes2, 
User3    Yes2, 
User4    
etc...
 

Trying to translate that over to SQL is where I'm stuck. I thought about something like:

CASE WHEN column1 = true THEN 'yes1, ' + CASE WHEN column2 = true THEN 'yes2, ' ELSE 'no' END AS Results
 

That isn't working. It appears that if the first criteria is met it uses it and then adds the else result to the end of it.

 UserID   Results
User1    yes1, no
User2    yes1, no
User3    
User4    
 

Is there something other then CASE that I should be using? Basically I want to evaluate multiple true/false fields and show all trues in one column separated by commas.