[GENERAL] Crosstab function

[Hengky Liwandouw]

Hi Friends, 

 

Could somebody help me with crosstab function ?  I have warehouse table:

 

CREATE TABLE tblwarehouse (

    id integer NOT NULL,

    warehousename character varying(20)

);

 

COPY tblwarehouse (id, warehousename) FROM stdin;

2     OFFICE

3     STORE2

\.

 

And product table :

 

CREATE TABLE tblproduct (

    id serial NOT NULL,

    produkid text,

    warehouseid integer,

    onhand integer

);

 

COPY tblproduct (produkid, warehouseid, onhand) FROM stdin;

2791404000014     2     10

2791404000021     3     10

2791404000014     3     45

\.

 

I need crosstab function to display record from tblproduct like this :

 

   PRODUKID    | OFFICE | STORE2 | TOTAL

---------------+--------+--------+ ------

 2791404000014 |     10 |     45 |   55

 2791404000021 |      0 |     10 |   10

 

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.

 

 

 

Thanks in advance !