Thread: 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

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