Message-ID: <443E99C5.3080800@pinpointresearch.com>
Date: Thu, 13 Apr 2006 11:34:45 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041207)
MIME-Version: 1.0
To: marchesini@unipg.it
CC: pgsql-sql@postgresql.org
Subject: Re: how to solve this problem
References: <1144934290.5079.32.camel@geologia5.agora.ing.unipg.it>
In-Reply-To: <1144934290.5079.32.camel@geologia5.agora.ing.unipg.it>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

> 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