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 !