Home > mailing lists

Thread: How to create crosstab with 3 values in every crosstab column

How to create crosstab with 3 values in every crosstab column

From

"Andrus"

Date:

06 February 2012, 14:23:44

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.

Re: How to create crosstab with 3 values in every crosstab column

From

Misa Simic

Date:

06 February 2012, 15:02:10

Hm...

I am not sure it is possible at all and with just 1 column for crosstab (instead of 3) to return table with undefined No of columns (to number of stores do not be hardcoded)...

At least you must define your return type...

Problem is known to me, and we have solved it on some way... which is not acceptable as generic solution... I mean - works just with our app...

So, we are doing that in two steps...

first calculate how much columns we should return...No of stores in in your Sales table (multiply 3 in your case)...

(and based on that - build the grid in UI)

then with knowing that - we select full "table" with simple 3 functions: CalcSales(coount, Store), getBudget(account, Store)

and then build dynamic query with those 3 functions many times as we have stores as columns...

Kind Regards,

Misa

2012/2/6 Andrus <kobruleht2@hot.ee>

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.

Re: How to create crosstab with 3 values in every crosstab column

From

"Andrus"

Date:

06 February 2012, 15:29:22

Thank you.

>first calculate how much columns we should return...No of stores in in your
>Sales table (multiply 3 in your case)...
>(and based on that - build the grid in UI)
>then with knowing that - we select full "table"  with simple 3 functions:
>CalcSales(coount, Store), getBudget(account, Store)
>and then build dynamic query with those 3 functions many times as we have
>stores as columns...

My goal is to open result En excel.
Instead of building dynamic query isn't it reasonable to create csv file
directly from code without using crosstab
but creating it manually ?
It looks that crosstab does not have any advantages instead on manual
crosstab creation ?

Another possibility is to merge those 3 columns into single column and
crosstab it .
After that create csv file by splitting single column into 3 columns.

Can some postgres array function or something like used to split 1 column
into 3 columns in result without
building query string manually ?

I posted this also in

http://stackoverflow.com/questions/9162497/how-to-generate-crosstab-with-3-columns-for-every-store-in-postgresql

Andrus.

Re: How to create crosstab with 3 values in every crosstab column

From

Misa Simic

Date:

06 February 2012, 16:38:23

Hi,

I think you dont understand me (or I do not understand you :) )

the point is - it is not possible to get unknown no of columns in 1 SQL query...

i.e.

Account, Store, Amount

100, St1, 1000.00

100, St2, 2000.00

to get:

Acount, St1 , St2

100, 1000.00 2000.00

to get that in your query... St1 and St2 - must be hardcoded... (is there 1 column per Store, or 3 columns per store it is less important...)

if it St1 and St2 are hardcoded in query, even if in table is:

Account, Store, Amount

100, St1, 1000.00

100, St2, 2000.00

100, St3, 3000.00

We would get the same result...actually if we want St3 we need to change our query and add St3 in it...

that is the reason why we use Dynamic SQL to build the query...

when you build your Dynamic SQL query... you could use COPY (dynamicQuery) TO CSV file...

or instead of to build dynamic query, you can export directly to file...

So still we need our functions: CalcSales(acount, Store), getBudget(account, Store): calcPercent(account, Store)

make export function in some procedural language you are familiar with... (plpgql, python, perl... whatever - just pure SQL is not possible...)

wich will:

loop trough distinict Accounts from Sales Table
inside Accounts loop,
WriteToFile(Account), then loop trough distinct Stores from Sales Table
inside Stores Loop
WriteToFile(';' + Store)
WriteToFile(';' + calcSales(acount, Store)
WriteToFile(';' + getBudget(acount, Store)
WriteToFile(';' + calcPercent(acount, Store)
after Stores loop make new line in file
and after Accounts loop close the file..

Optionally you can first loop trough Stores loop to create header line...

Kind Regards,

Misa

that is the reason why we use dynamic query...

2012/2/6 Andrus <kobruleht2@hot.ee>

Thank you.

first calculate how much columns we should return...No of stores in in your
Sales table (multiply 3 in your case)...
(and based on that - build the grid in UI)
then with knowing that - we select full "table" with simple 3 functions:
CalcSales(coount, Store), getBudget(account, Store)
and then build dynamic query with those 3 functions many times as we have
stores as columns...

My goal is to open result En excel.
Instead of building dynamic query isn't it reasonable to create csv file
directly from code without using crosstab
but creating it manually ?
It looks that crosstab does not have any advantages instead on manual
crosstab creation ?

Another possibility is to merge those 3 columns into single column and
crosstab it .
After that create csv file by splitting single column into 3 columns.

Can some postgres array function or something like used to split 1 column
into 3 columns in result without
building query string manually ?

I posted this also in

http://stackoverflow.com/questions/9162497/how-to-generate-crosstab-with-3-columns-for-every-store-in-postgresql

Andrus.

Re: How to create crosstab with 3 values in every crosstab column

From

"Andrus"

Date:

06 February 2012, 17:02:33

Thank you.

> the point is - it is not possible to get unknown no of columns in 1 SQL
> query...
>i.e.
>Account, Store, Amount
>100, St1, 1000.00
>100, St2, 2000.00
>to get:
>Acount,    St1     ,      St2
>100,     1000.00      2000.00
>to get that in your query... St1 and St2 - must be hardcoded... (is there 1
>column per Store, or 3 columns per store it >is less important...)
>if it St1 and St2 are hardcoded in query, even if in table is:
>Account, Store, Amount
>100, St1, 1000.00
>100, St2, 2000.00
>100, St3, 3000.00
>We would get the same result...actually if we want St3 we need to change
>our query and add St3 in it...
>that is the reason why we use Dynamic SQL to build the query...
>when you build your Dynamic SQL query... you could use COPY (dynamicQuery)
>TO CSV file...
>or instead of to build dynamic query, you can export directly to file...

I din't knwo this. This seems very serious limitation which makes crosstab
useless .
I tried

create temp table sales (
  account char(10),
  store char(10),
  sales  numeric(12,2) ) on commit drop;

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

select * from
crosstab('select * from sales', 'select distinct store from sales' ) x

and got error

ERROR:  a column definition list is required for functions returning
"record"

Can we use something like

select * from
  dynamicwrapper( crosstab('select * from sales', 'select distinct store
from sales' ))  x

Where to find generic dynamicwrapper stored procedure which fixes this by
building dynamic query itself or other idea ?

Andrus.

Re: How to create crosstab with 3 values in every crosstab column

From

Misa Simic

Date:

06 February 2012, 17:52:14

Hi,

Well, I think you will need to write your own function(s) which will solve your particular case...

There are two ways explaind in last mails... Dynamic SQL or direct export to file...

Kind Regards,

Misa

2012/2/6 Andrus <kobruleht2@hot.ee>

Thank you.

the point is - it is not possible to get unknown no of columns in 1 SQL query...
i.e.
Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
to get:
Acount, St1 , St2
100, 1000.00 2000.00
to get that in your query... St1 and St2 - must be hardcoded... (is there 1 column per Store, or 3 columns per store it >is less important...)
if it St1 and St2 are hardcoded in query, even if in table is:
Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
100, St3, 3000.00
We would get the same result...actually if we want St3 we need to change our query and add St3 in it...
that is the reason why we use Dynamic SQL to build the query...
when you build your Dynamic SQL query... you could use COPY (dynamicQuery) TO CSV file...
or instead of to build dynamic query, you can export directly to file...

I din't knwo this. This seems very serious limitation which makes crosstab useless .
I tried

create temp table sales (
account char(10),
store char(10),
sales numeric(12,2) ) on commit drop;

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

select * from
crosstab('select * from sales', 'select distinct store from sales' ) x

and got error

ERROR: a column definition list is required for functions returning "record"

Can we use something like

select * from
dynamicwrapper( crosstab('select * from sales', 'select distinct store from sales' )) x

Where to find generic dynamicwrapper stored procedure which fixes this by building dynamic query itself or other idea ?

Andrus.

Re: How to create crosstab with 3 values in every crosstab column

From

"Marc Mamin"

Date:

06 February 2012, 19:01:51

Hello,

as you don't seems to need the returned column definition in Postgres, a solution may be to cast the result to text.

e.g.:

create or replace function get_record ()

returns setof text as

select (foo)::text from

(values(1,'a a'),(3,'b b'))foo

language sql;

select trim(r,'\\(\\)') from get_record () r;

regards,

Marc Mamin

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Misa Simic
Sent: Montag, 6. Februar 2012 19:52
To: Andrus
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to create crosstab with 3 values in every crosstab column

Hi,

Well, I think you will need to write your own function(s) which will solve your particular case...

There are two ways explaind in last mails... Dynamic SQL or direct export to file...

Kind Regards,

Misa

2012/2/6 Andrus <kobruleht2@hot.ee>

Thank you.

the point is - it is not possible to get unknown no of columns in 1 SQL query...
i.e.
Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
to get:
Acount, St1 , St2
100, 1000.00 2000.00
to get that in your query... St1 and St2 - must be hardcoded... (is there 1 column per Store, or 3 columns per store it >is less important...)
if it St1 and St2 are hardcoded in query, even if in table is:
Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
100, St3, 3000.00
We would get the same result...actually if we want St3 we need to change our query and add St3 in it...
that is the reason why we use Dynamic SQL to build the query...
when you build your Dynamic SQL query... you could use COPY (dynamicQuery) TO CSV file...
or instead of to build dynamic query, you can export directly to file...

I din't knwo this. This seems very serious limitation which makes crosstab useless .
I tried

create temp table sales (
account char(10),
store char(10),
sales numeric(12,2) ) on commit drop;

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

select * from
crosstab('select * from sales', 'select distinct store from sales' ) x

and got error

ERROR: a column definition list is required for functions returning "record"

Can we use something like

select * from
dynamicwrapper( crosstab('select * from sales', 'select distinct store from sales' )) x

Where to find generic dynamicwrapper stored procedure which fixes this by building dynamic query itself or other idea ?

Andrus.