Search code examples
sqlpostgresqlunpivot

How to turn columns into row condition

I have a column structure like so:

ID, c1(boolean), c2(boolean), c3(boolean), c4(boolean).

How can I turn the booleans into row conditions while reading from them? I only want the columns that were true.

i.e.

ID | c1 | c2 | c3 | c4 | c5
107 true true false true false

I'd want to only return something like this:

ID | col
1   c1
1   c2
1   c4

I'm not sure if there's something like that in postgres.

Solution

  • I wouldn't use arrays for this. I would simply do:

    select t.id, v.colname
from t cross join lateral
     (values (c1, 'c1'), (c2, 'c2'), (c3, 'c3'), (c4, 'c4'), (c5, 'c5')
     ) v(val, colname)
where v.val;

    This allows you to name your columns whatever you want.