> 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
whenvinc20 = 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