Re: Crosstab function - Mailing list pgsql-general

> Are you sure that there is no pure sql solution for this ?

There is no pure SQL solution because a SQL query always gives a fixed
number of columns. You could compose the SQL in your client app and
vary the columns by the current warehouses. Or you could say GROUP BY
produkit, tblwarehouse.id and rearrange the result client-side. I'd
say those are the practical approaches, but if they aren't possible,
you may also be able to use Postgres's array feature, so that your
result columns are:

produkid
warehouse_totals[]
total

Someone asked a similar question about a year ago, and I wrote up how
to solve it with arrays and a recursive CTE here:

http://www.illuminatedcomputing.com/posts/2013/03/fun-postgres-puzzle/

Good luck,
Paul


On Mon, May 5, 2014 at 7:37 PM, Hengky Liwandouw
<hengkyliwandouw@gmail.com> wrote:
> Hi David,
>
> Are you sure that there is no pure sql solution for this ?
>
> I think (with my very limited postgres knowledge), function can solve this.
>
> So far i can use command:
>
> select *
> from crosstab
> (
>   'select produkid, warehouseid, onhand
>    from tblproduct order by 1',
>   'select distinct warehouseid from tblproduct order by 1'
> )
> as ct (produkid text, office int, store2 int);
>
> and I have this result :
>
>    PRODUKID    | OFFICE | STORE2 |
> ---------------+--------+--------+
>  2791404000014 |     10 |     45 |
>  2791404000021 |        |     10 |
>
> The problem is the column header is static. If I have new warehouse, I
> should manually add it in the column header.
>
> IF I use command : select 'Produk ID text, ' || array_to_string(array(select
> warehousename from tblwarehouse), ' int, ') || ' int';
>
> I can get : "Produk ID text, OFFICE int, STORE2 int"
>
> Which is the column header I need but I really have no idea how to use this
> as column header.
>
> Anyway, If i can't do this in postgres, I will try to build sql string in
> the client application (Windev) and send the fixed sql to the server
>
> Thanks
>
>
>
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David G Johnston
> Sent: Monday, May 05, 2014 10:25 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Crosstab function
>
> Hengky Lie wrote
>> The crosstab warehouse column name (OFFICE & Store2) is taken from
>> tblwarehouse so when user add warehouse, crosstab column name will
>> change automatically. And also each row has total qty.
>
> In what programming language?  The only way to do this is to dynamically
> construct the appropriate query, with the correct number of columns,
> on-the-fly in the client application and send it as a normal query to the
> server.  There is no pure SQL solution.
>
> For the total column you will need a virtual warehouse that holds those
> values.  Likely the easiest way to get that will be to UNION ALL the main
> real warehouse query and another query the groups by product and sum-counts
> that values from the individual warehouses.
>
> IIRC you've already been shown how to write the basic crosstab query; this
> really isn't any different but you will need procedural logic and some way
> to dynamically build a SQL query string based upon how many warehouses you
> have at the time you run the query.
>
> I am assuming you know how to write the basic join query to get the general
> form needed for the real warehouse data.
>
> David J.
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



--
_________________________________
Pulchritudo splendor veritatis.