[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:

Expand|Select|Wrap|Line Numbers

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

Below are the results I would get.

Expand|Select|Wrap|Line Numbers

1. UserID   Results
2. User1    Yes1, 
3. User2    Yes1, Yes2, 
4. User3    Yes2, 
5. User4    
6. etc...
7.  

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

Expand|Select|Wrap|Line Numbers

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

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.

Expand|Select|Wrap|Line Numbers

1. UserID   Results
2. User1    yes1, no
3. User2    yes1, no
4. User3    
5. User4    
6.  

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.