I have a table where there are 20 columns named
vinc1, vinc2, vinc3, vinc4, etc....
the values contained into each column are simply 1 or 0 (each column is
dichotomic)
1 means presence
0 means absence
I would obtain a column (new_column) containg the name of the columns,
comma separated, where the value is = 1
for example:
vinc1 vinc2 vinc3 vinc4 new_column
1 0 1 0 vinc1,vinc3
0 0 0 1 vinc4
0 1 1 1 vinc2,vinc3,vinc4
can someone help me to find the best way to obtain this result???
thank you very much
Here's a brute-force method. Maybe someone else has a more elegant way.
More info on the nature of your data and what you are trying to obtain
from it would help in finding such elegance.
select
substr(
case when vinc1 = 1 then ',vinc1' else '' ||
case when vinc2 = 1 then ',vinc2' else '' ||
...
case when vinc20 = 1 then ',vinc20' else ''
,2) as new_column ...
As to the binary representation mentioned elsewhere, the idea is that
you can view vinc1-vinc20 as bits in a binary number equal to vinc1 +
2*vinc2 + 4*vinc3 + 8*vinc4...2^19*vinc20. Whether or not this is useful
depends on what you are trying to do.
Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
