pivot functions with variable number of columns

On Thu, Sep 6, 2012 at 10:44 AM, punnoose <punnoose.pj@dwisesolutions.com>wrote:

I want to have a pivot like function in which i should have variable number
of columns.i went for crosstab but it doesnot support variable number of
columns.Can any body suggest an alternative.like if i have a event at a
particular time of the day like one at 02:35,11:34, then i should have
column name 02:35,11:34.
Please do help me.
Punnoose

Am not sure how your table structure is designed to use the best of
crosstab. Here is a simple example to give some light on how to work with
crosstab it.

*Table & Data:*

CREATE TABLE pivot_test (id integer, customer_id integer, product_code
VARCHAR, quantity integer);

INSERT INTO pivot_test VALUES (1, 1, 'A', 10);

INSERT INTO pivot_test VALUES (2, 1, 'B', 20);

INSERT INTO pivot_test VALUES (3, 1, 'C', 30);

INSERT INTO pivot_test VALUES (4, 2, 'A', 40);

INSERT INTO pivot_test VALUES (5, 2, 'C', 50);

INSERT INTO pivot_test VALUES (6, 3, 'A', 60);

INSERT INTO pivot_test VALUES (7, 3, 'B', 70);

INSERT INTO pivot_test VALUES (8, 3, 'C', 80);

INSERT INTO pivot_test VALUES (9, 3, 'D', 90);

INSERT INTO pivot_test VALUES (10, 4, 'A', 100);

postgres=# select * from pivot_test;

id | customer_id | product_code | quantity

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

1 | 1 | A | 10

2 | 1 | B | 20

3 | 1 | C | 30

4 | 2 | A | 40

5 | 2 | C | 50

6 | 3 | A | 60

7 | 3 | B | 70

8 | 3 | C | 80

9 | 3 | D | 90

10 | 4 | A | 100

(10 rows)

*Here is Pivot kind result:*

postgres=select * from crosstab

('select customer_id::text,

product_code::text,

quantity::text

from pivot_test

where product_code=''A'' or product_code=''B'' or
product_code=''C''

order by 1,2'

) as ct(customer_id text, "A" text,"B" text,"C" text);

customer_id | A | B | C

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

1 | 10 | 20 | 30

2 | 40 | 50 |

3 | 60 | 70 | 80

4 | 100 | |

(4 rows)
Someone, might have better example. Timely you can work with above example.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Wed, Sep 5, 2012 at 10:14 PM, punnoose <punnoose.pj@dwisesolutions.com>wrote:

I want to have a pivot like function in which i should have variable number
of columns.i went for crosstab but it doesnot support variable number of
columns.Can any body suggest an alternative.like if i have a event at a
particular time of the day like one at 02:35,11:34, then i should have
column name 02:35,11:34.

You could detect the columns you want to return and use a plpgsql function

that returns a refcursor, I suppose.

Best Wishes,
Chris travers

Le jeudi 06 septembre 2012 ￜ 00:40 -0700, Chris Travers a ￜcrit :

On Wed, Sep 5, 2012 at 10:14 PM, punnoose
<punnoose.pj@dwisesolutions.com> wrote:
I want to have a pivot like function in which i should have
variable number
of columns.i went for crosstab but it doesnot support variable
number of
columns.Can any body suggest an alternative.like if i have a
event at a
particular time of the day like one at 02:35,11:34, then i
should have
column name 02:35,11:34.

You could detect the columns you want to return and use a plpgsql
function that returns a refcursor, I suppose.

Below is an example in Perl : it selects the values in column
'time_of_day' from 'your_table' and builds a table named 'crosstab' with
the proper column names. You can start from this and adjust to your
needs.

If at all possible, I find a good solution to these problems is to
provide an easy way for your users to download the data in csv format;
that way they can import it into their office suite for processing there
(MS-Access, OpenOffice have crosstab queries)

CREATE OR REPLACE FUNCTION build_crosstab ( ) RETURNS VOID AS $$

my @field_names;
my $field_list;

#la requï¿œte qui ramï¿œne les donnï¿œes
my $rv = spi_exec_query("SELECT time_of_day FROM your_table GROUP BY
time_of_day ORDER BY 1");

#exï¿œcuter la requï¿œte, compter les lignes
my $nrows = $rv->{processed};

#pour chaque ligne, imprimer le nom
foreach my $rn (0 .. $nrows - 1) {

my $row = $rv->{rows}[$rn];

push @field_names, '"' . $row->{time_of_day} . '"' ;

}

for ( @field_names ) {

$field_list .= ', ' . $_ . ' text';

}

my $create_table = 'CREATE TABLE crosstab (' . substr($field_list, 1) .
')';

my $action = spi_exec_query($create_table);

$$ LANGUAGE plperlu;

--
Vincent Veyron
http://marica.fr/
Gestion informatisï¿œe des dossiers contentieux et des sinistres assurances pour le service juridique

a very nice way is to use a cursor.
http://okbob.blogspot.cz/2008/08/using-cursors-for-generating-cross.html

HTH

WBL

On Thu, Sep 6, 2012 at 12:40 PM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:

Le jeudi 06 septembre 2012 à 00:40 -0700, Chris Travers a écrit :

On Wed, Sep 5, 2012 at 10:14 PM, punnoose
<punnoose.pj@dwisesolutions.com> wrote:
I want to have a pivot like function in which i should have
variable number
of columns.i went for crosstab but it doesnot support variable
number of
columns.Can any body suggest an alternative.like if i have a
event at a
particular time of the day like one at 02:35,11:34, then i
should have
column name 02:35,11:34.

You could detect the columns you want to return and use a plpgsql
function that returns a refcursor, I suppose.

Below is an example in Perl : it selects the values in column
'time_of_day' from 'your_table' and builds a table named 'crosstab' with
the proper column names. You can start from this and adjust to your
needs.

If at all possible, I find a good solution to these problems is to
provide an easy way for your users to download the data in csv format;
that way they can import it into their office suite for processing there
(MS-Access, OpenOffice have crosstab queries)

CREATE OR REPLACE FUNCTION build_crosstab ( ) RETURNS VOID AS $$

my @field_names;
my $field_list;

#la requête qui ramène les données
my $rv = spi_exec_query("SELECT time_of_day FROM your_table GROUP BY
time_of_day ORDER BY 1");

#exécuter la requête, compter les lignes
my $nrows = $rv->{processed};

#pour chaque ligne, imprimer le nom
foreach my $rn (0 .. $nrows - 1) {

my $row = $rv->{rows}[$rn];

push @field_names, '"' . $row->{time_of_day} . '"' ;

}

for ( @field_names ) {

$field_list .= ', ' . $_ . ' text';

}

my $create_table = 'CREATE TABLE crosstab (' . substr($field_list, 1) .
')';

my $action = spi_exec_query($create_table);

$$ LANGUAGE plperlu;

--
Vincent Veyron
http://marica.fr/
Gestion informatisée des dossiers contentieux et des sinistres assurances
pour le service juridique

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

That is one of most wanted features of PostgreSQL, what is not solved yet,,,

But it seems will be soon with introductions of Stored Procedures...

For now, you must "know" what result (columns) you expects...

So the only one option for now is to use Dynamic SQL - to build your query
dynamically based on data in your table (based on column what should be
pivoted)..

And then execute that query...

You can use your client side language to build SQL or inisde DB you could
make function what returns "text" as your Dynamic SQL and then execute it
from your client...

Kind Regards,

Misa

2012/9/6 punnoose <punnoose.pj@dwisesolutions.com>

Hello

2012/9/6 Misa Simic <misa.simic@gmail.com>:

That is one of most wanted features of PostgreSQL, what is not solved yet,,,

But it seems will be soon with introductions of Stored Procedures...

I wish :)

For now, you must "know" what result (columns) you expects...

So the only one option for now is to use Dynamic SQL - to build your query
dynamically based on data in your table (based on column what should be
pivoted)..

And then execute that query...

You can use your client side language to build SQL or inisde DB you could
make function what returns "text" as your Dynamic SQL and then execute it
from your client...

there is a some workaround

http://okbob.blogspot.cz/2008/08/using-cursors-for-generating-cross.html

Pavel

Hi Pavel,

Hm... To me workaround looks as exactly as the same thing?

1) uses Dynamic SQL to bulid query (but returns refcursor insted of text)

2) client still needs to execute 2 commands (second is fetch instead of
execute 'result')

However, based on your name, and the name of the blog author :) I have made
conlusion you are working on Stored Procedures things?

I have a few questions about that... But will send another mail to dont mix
subjects...

Cheers,

Misa

On Thursday, September 6, 2012, Pavel Stehule wrote:

2012/9/6 Misa Simic <misa.simic@gmail.com>:

Hi Pavel,

Hm... To me workaround looks as exactly as the same thing?

1) uses Dynamic SQL to bulid query (but returns refcursor insted of text)

2) client still needs to execute 2 commands (second is fetch instead of
execute 'result')

However, based on your name, and the name of the blog author :) I have made
conlusion you are working on Stored Procedures things?

I worked on stored procedures - but not now - I have too much opened
projects, and I would to finish it before - this claim is free now :)
I am working (too slowly :( on PSM language).

Regards

Pavel