SUM and concatenation

Question

I have the following table:

customer1	apple	pear	grape	orange
1	1	0	0	1
2	0	1	1	1
3	0	0	0	1
4	0	0	0	1

And I want a column where 1 is set if the client only has oranges and another column where a concatenation is made of the fruits they have separated by "," for example:

customer1	apple	pear	grape	orange	only_orange	fruits
1	1	0	0	1	0	apple, orange
2	0	1	1	1	0	pear, grape, orange
3	0	0	0	1	1	orange
4	0	0	0	1	1	orange

What I did was make a query where I would get the clients that only have oranges and I put it in a temporary table

SELECT [customer1] 
INTO #tmp1
FROM customer_fruits
WHERE [apple] = 0 AND [pear] = 0 AND [grape] = 0 AND [orange] = 1

Later I did a left join with my original table and changed the nulls to "0", obtaining the following table, although I reached the result, I don't know if there is another way but I couldn't do the concatenation part anymore.

Answer 1

Try following query

SELECT 
    customer1, 
    apple, 
    pear, 
    grape, 
    orange,
    CASE 
        WHEN orange = 1 AND (apple = 1 OR pear = 1 OR grape = 1) 
            THEN 0 
        WHEN orange = 1 
            THEN 1 
        ELSE 0 
    END as only_orange,
    CONCAT_WS(',',
    CASE WHEN apple = 1 THEN 'apple' ELSE NULL END,
    CASE WHEN pear = 1 THEN 'pear' ELSE NULL END,
    CASE WHEN grape = 1 THEN 'grape' ELSE NULL END,
    CASE WHEN orange = 1 THEN 'orange' ELSE NULL END) as fruits
FROM 
    fruits;