How to create crosstab with 3 values in every crosstab column

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: <pgsql-general(at)postgresql(dot)org>
Subject: How to create crosstab with 3 values in every crosstab column
Date: 2012-02-06 15:23:19
Message-ID: 7D0C51B0299E43DA83C8F8A24698008A@dell2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm looking for a way to generate cross tab with 3 columns for every store where
number of stores in not hard coded.
Every store info should contain 3 columns:

turnover
budget
budget percent (=turnover/budget*100)

Result should look like:

Acc st1turnover st1budget st1percent ... stNturnover st1budget stNpercent
311 100 200 50 200 ... 300 67
312 400 500 80 600 ... 700 86
...

I tried crosstab from tablefunc but it allows only single value in every crosstabled column.

How to show 3 values in every column: sales, budget and percent in this order?

Tables are:

create table sales (
account char(10),
store char(10),
sales numeric(12,2) );

insert into sales values
('311','ST1',100)... ('311','STN',200)
('312','ST1',400)... ('312','STN',600);

create table budget (
account char(10),
store char(10),
budget numeric(12,2) );

insert into budger values
('311','ST1',200)... ('311','STN',300)
('312','ST1',500)... ('312','STN',700);

Some account and store values may be missing from tables.

Andrus.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Misa Simic 2012-02-06 16:01:53 Re: How to create crosstab with 3 values in every crosstab column
Previous Message Merlin Moncure 2012-02-06 14:34:56 Re: Puzzling full database lock