From: | Mark Roberts <mailing_lists(at)pandapocket(dot)com> |
---|---|
To: | Teemu Juntunen <teemu(dot)juntunen(at)e-ngine(dot)fi> |
Cc: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SELECT query experts, anyone? |
Date: | 2008-08-20 18:02:25 |
Message-ID: | 1219255345.14010.77.camel@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
If you put this in the application, you could do something such as:
my @manufacturers = fetch("select manufacturer_no, name from
manufacturers");
my @select_fields = ('product_no');
foreach my $manufacturer (@manufacturers) {
my $manuf_no = $manufacturer->{manufacturer_no};
my $name = $manufacturer->{name};
push(@select_fields, "case when x.manufacturer_no = $manuf_no then
'$name' else null end as manuf_${manuf_no}_products);
}
my @outer_select_fields = ('product_no', map { my $manuf = "manuf_" .
$_->{manufacturer_no} . "_products"; "sum($manuf) as $manuf" }
@manufacturers);
my @dataset = fetch("
select @{[ join(",\n", @outer_select_fields) ]}
from (
select @{[ join(",\n", @select_fields) ]}
from products_by_manufacturer x
) x
group by product_no
Uh, or something like that. Perl in Evolution is really.. painful.
-Mark
On Wed, 2008-08-20 at 15:50 +0300, Teemu Juntunen wrote:
> Hi Experts,
>
> is threre any way to SELECT values in columns instead of rows? For
> example select products and their manufacters in the followin way:
>
> product1; manufacturer1; manufacturer2;,,, manufacturerN
> product2; manufacturer3;
> product3; manufacturer1;.. manufacturerN-1
>
> With a function you could put the product manufacturers in one string,
> but I would like to have them in columns.
>
> How about arrays. Is there a way to SELECT values in an array to
> columns?
>
> Best regards and thanks,
> Teemu Juntunen
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2008-08-20 18:27:34 | Re: Fwd: Restarting with pg_ctl, users, and passwords. |
Previous Message | Blakely, Jerel (Mission Systems) | 2008-08-20 17:43:44 | Re: Silent install 8.3 diiffers from 8.2 |