From: Steve Crawford <scrawford@pinpointresearch.com>
To: marchesini@unipg.it
Cc: pgsql-sql@postgresql.org
Subject: Re: how to solve this problem
Date: Thu, 13 Apr 2006 11:34:45 -0700
Message-ID: <443E99C5.3080800@pinpointresearch.com> (raw)
In-Reply-To: <1144934290.5079.32.camel@geologia5.agora.ing.unipg.it>
References: <1144934290.5079.32.camel@geologia5.agora.ing.unipg.it>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, marchesini@unipg.it
Subject: Re: how to solve this problem
In-Reply-To: <443E99C5.3080800@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox