SELECT query experts, anyone?

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?

This should work:

SELECT product,array(SELECT manufacturer FROM manufacturers WHERE
manufacturer_id=products.manufacturer_id) WHERE ...

or something along the lines of the above.

Regards
Tino

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