Re: SELECT query experts, anyone?

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