Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to solve this problem

how to solve this problem

From
ivan marchesini
Date:
Dear users,
I have this problem 

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

Ivan







-- 
Ivan Marchesini
Department of Civil and Environmental Engineering
University of Perugia
Via G. Duranti 93/a 
06125
Perugia (Italy)
e-mail: marchesini@unipg.it       ivan.marchesini@gmail.com
tel: +39(0)755853760
fax: +39(0)755853756
jabber: geoivan73@jabber.org

Re: how to solve this problem

From
Richard Broersma Jr
Date:
Just a thought,

Could you achieve that same result using the binary representation of an integer?

Regards,

Richard

--- ivan marchesini <marchesini@unipg.it> wrote:

> Dear users,
> I have this problem 
> 
> 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
> 
> Ivan
> 
> 
> 
> 
> 
> 
> 
> -- 
> Ivan Marchesini
> Department of Civil and Environmental Engineering
> University of Perugia
> Via G. Duranti 93/a 
> 06125
> Perugia (Italy)
> e-mail: marchesini@unipg.it
>         ivan.marchesini@gmail.com
> tel: +39(0)755853760
> fax: +39(0)755853756
> jabber: geoivan73@jabber.org
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

Re: how to solve this problem

From
ivan marchesini
Date:
Sorry, I'm not able to understand how I can use this solution!!!
can you better explain me your suggestion???

Thanks very much


Il giorno gio, 13/04/2006 alle 06.51 -0700, Richard Broersma Jr ha
scritto:
> Just a thought,
> 
> Could you achieve that same result using the binary representation of an integer?
> 
> Regards,
> 
> Richard
> 
> --- ivan marchesini <marchesini@unipg.it> wrote:
> 
> > Dear users,
> > I have this problem 
> > 
> > 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
> > 
> > Ivan
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > -- 
> > Ivan Marchesini
> > Department of Civil and Environmental Engineering
> > University of Perugia
> > Via G. Duranti 93/a 
> > 06125
> > Perugia (Italy)
> > e-mail: marchesini@unipg.it
> >         ivan.marchesini@gmail.com
> > tel: +39(0)755853760
> > fax: +39(0)755853756
> > jabber: geoivan73@jabber.org
> > 
> > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to majordomo@postgresql.org so that your
> >        message can get through to the mailing list cleanly
> > 
> 
> 
-- 
Ivan Marchesini
Department of Civil and Environmental Engineering
University of Perugia
Via G. Duranti 93/a 
06125
Perugia (Italy)
e-mail: marchesini@unipg.it       ivan.marchesini@gmail.com
tel: +39(0)755853760
fax: +39(0)755853756
jabber: geoivan73@jabber.org

Re: how to solve this problem

From
Steve Crawford
Date:
> 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

Re: how to solve this problem

From
ivan marchesini
Date:
Thank you very much!!!
your suggestion seems really usefull!! I will try it very soon!!!
ivan


Il giorno gio, 13/04/2006 alle 11.34 -0700, Steve Crawford ha scritto:
> > 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
> 
-- 
Ivan Marchesini
Department of Civil and Environmental Engineering
University of Perugia
Via G. Duranti 93/a 
06125
Perugia (Italy)
e-mail: marchesini@unipg.it       ivan.marchesini@gmail.com
tel: +39(0)755853760
fax: +39(0)755853756
jabber: geoivan73@jabber.org

Re: how to solve this problem

From
Luckys
Date:
the same can be even achieved, instead of having 20 different columns,
in a single column varchar(20) vincs('11010111011101110010').
 
Luckys.
 
On 4/14/06, ivan marchesini <marchesini@unipg.it> wrote:
Thank you very much!!!
your suggestion seems really usefull!! I will try it very soon!!!
ivan


Il giorno gio, 13/04/2006 alle 11.34 -0700, Steve Crawford ha scritto:
> > 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
>
--
Ivan Marchesini
Department of Civil and Environmental Engineering
University of Perugia
Via G. Duranti 93/a
06125
Perugia (Italy)
e-mail: marchesini@unipg.it
       ivan.marchesini@gmail.com
tel: +39(0)755853760
fax: +39(0)755853756
jabber: geoivan73@jabber.org




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings