Concat by Column Names using Binary Results

I'd like to concat column names if true as per below:

Input Table:

Desired Output Table:

The following query using a CASE expression and the CONCAT function shall work for you in snowflake.

SELECT 
    ID, 
    RTRIM(CONCAT(
        CASE WHEN Flag1 THEN 'Flag1, ' ELSE '' END,
        CASE WHEN Flag2 THEN 'Flag2, ' ELSE '' END,
        CASE WHEN Flag3 THEN 'Flag3, ' ELSE '' END
    ), ', ') AS Flags
FROM 
    YourTable;

Output :

As I don't have any free tool to show you demo; I am trying to show you demo here using equivalent query in other database

so chaining a few commands together, IFF to test the column value and produce the name or null, ARRAY_CONSTRUCT_COMPACT to strip out the null values, and ARRAY_TO_STRING to put it together nicely.

and using a CTE for data:

with data(id, flag1, flag2, flag3) as (
    select * from values
    (1,	True,	False,	True),
    (2,	False,	True,	True)
)
select 
    id
    ,iff(flag1,'flag1', null) as c1
    ,iff(flag2,'flag2', null) as c2
    ,iff(flag3,'flag3', null) as c3
    ,array_construct_compact(c1,c2,c3) as a
    ,array_to_string(a, ', ') as result
from data
order by 1;

which can be folded up like:

select 
    id
    ,array_to_string( 
        array_construct_compact(
            iff(flag1,'flag1', null),
            iff(flag2,'flag2', null), 
            iff(flag3,'flag3', null)
            )
        , ', '
        ) as flags
from data
order by 1;

to give:

You can use a combination of the CASE statement, CONCAT function, and string aggregation function like you can use the LISTAGG function.

Here's an example SQL query:

SELECT ID, LISTAGG(CASE
                WHEN Flag1 = TRUE THEN 'Flag1'
                ELSE NULL
              END || ', ' ||
              CASE
                WHEN Flag2 = TRUE THEN 'Flag2'
                ELSE NULL
              END || ', ' ||
              CASE
                WHEN Flag3 = TRUE THEN 'Flag3'
                ELSE NULL
              END, ', ') WITHIN GROUP (ORDER BY ID) AS Flags
FROM InputTable
GROUP BY ID;