CODEWITHSUNDEEP
sqlpostgresqlunpivot
Celestz
Celestz

Reputation: 311

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.

Upvotes: 1

Views: 77

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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.

Upvotes: 2

user330315
user330315

Reputation:

You can use an array that is unnested:

select id, 'c'||t.idx as col
from the_table
  cross join unnest(array[c1,c2,c3,c4]) with ordinality as t(flag, idx)
where t.flag;

with ordinality returns the index of the value from the array. As that corresponds to the column "index", we can "re-create" the column name by using that in the output.

Online example: https://rextester.com/JJHG50564

Upvotes: 3

Related Questions