pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to solve this problem
6+ messages / 4 participants
[nested] [flat]
* how to solve this problem
@ 2006-04-13 13:18 ivan marchesini <marchesini@unipg.it>
0 siblings, 2 replies; 6+ messages in thread
From: ivan marchesini @ 2006-04-13 13:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to solve this problem
@ 2006-04-13 13:51 Richard Broersma Jr <rabroersma@yahoo.com>
parent: ivan marchesini <marchesini@unipg.it>
1 sibling, 1 reply; 6+ messages in thread
From: Richard Broersma Jr @ 2006-04-13 13:51 UTC (permalink / raw)
To: marchesini@unipg.it; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to solve this problem
@ 2006-04-13 13:56 ivan marchesini <marchesini@unipg.it>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: ivan marchesini @ 2006-04-13 13:56 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to solve this problem
@ 2006-04-13 18:34 Steve Crawford <scrawford@pinpointresearch.com>
parent: ivan marchesini <marchesini@unipg.it>
1 sibling, 1 reply; 6+ messages in thread
From: Steve Crawford @ 2006-04-13 18:34 UTC (permalink / raw)
To: marchesini@unipg.it; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to solve this problem
@ 2006-04-14 15:40 ivan marchesini <marchesini@unipg.it>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 6+ messages in thread
From: ivan marchesini @ 2006-04-14 15:40 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to solve this problem
@ 2006-04-15 09:56 Luckys <plpgsql@gmail.com>
parent: ivan marchesini <marchesini@unipg.it>
0 siblings, 0 replies; 6+ messages in thread
From: Luckys @ 2006-04-15 09:56 UTC (permalink / raw)
To: marchesini@unipg.it; +Cc: Steve Crawford <scrawford@pinpointresearch.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-04-15 09:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-13 13:18 how to solve this problem ivan marchesini <marchesini@unipg.it>
2006-04-13 13:51 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-04-13 13:56 ` ivan marchesini <marchesini@unipg.it>
2006-04-13 18:34 ` Steve Crawford <scrawford@pinpointresearch.com>
2006-04-14 15:40 ` ivan marchesini <marchesini@unipg.it>
2006-04-15 09:56 ` Luckys <plpgsql@gmail.com>
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