Build comma-separated list of qualifying column names-CodePudding

I have 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

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

CodePudding user response：

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