Crosstab function - Mailing list pgsql-general

From Hengky Liwandouw
Subject Crosstab function
Date
Msg-id 000601cf682e$2dce68c0$896b3a40$@com
Whole thread Raw
Responses Re: Crosstab function  (David G Johnston <david.g.johnston@gmail.com>)
List pgsql-general

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 !

 

pgsql-general by date:

Previous
From: Glen Eustace
Date:
Subject: Re: Monitoring Pg servers with Microsoft SCOM
Next
From: Magnus Hagander
Date:
Subject: Re: Monitoring Pg servers with Microsoft SCOM