Issue

This Content is from Stack Overflow. Question asked by Amina Umar

I am having this table:

CREATE TABLE fruits(id int,apple varchar(50),pear varchar(50),
        orange varchar(50),grapes varchar(50),banana varchar(500))

INSERT INTO fruits(id, apple, pear, orange, grapes, banana)
VALUES (51,0,1,0,0,0), (52,0,1,0,0,1), (53,1,0,1,0,1),(54,1,0,0,0,1)

So that:

SELECT * FROM fruits
id  apple   pear    orange  grapes  banana
51    0      1        0       0      0
52    0      1        0       0      1
53    1      0        1       0      1
54    1      0        0       0      1

So I want to select the column names for which the value is 1, into a tab-separated csv file.

Intended file format:

51  pear
52  pear,banana
53  apple,orange,banana
54  apple,banana

Solution

A couple of CASE expressions, wrapped in concat_ws() to deal with null values properly:

SELECT id
     , concat_ws( ', '
        , CASE WHEN apple  = '1' THEN 'apple'  END
        , CASE WHEN pear   = '1' THEN 'pear'   END
        , CASE WHEN orange = '1' THEN 'orange' END
        , CASE WHEN banana = '1' THEN 'banana' END) AS fruit_list
FROM   fruits;

fiddle

See:

How to concatenate columns in a Postgres SELECT?

Of course, your columns should rather be boolean instead of varchar to begin with. Then it could be just:

CASE WHEN apple THEN 'apple' END

This Question was asked in StackOverflow by Amina Umar and Answered by Erwin Brandstetter It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.