Thread: pivot functions with variable number of columns

pivot functions with variable number of columns

From
punnoose
Date:
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




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pivot-functions-with-variable-number-of-columns-tp5723013.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: pivot functions with variable number of columns

From
Raghavendra
Date:

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

Re: pivot functions with variable number of columns

From
Chris Travers
Date:


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 

Re: pivot functions with variable number of columns

From
Vincent Veyron
Date:
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



Re: pivot functions with variable number of columns

From
Willy-Bas Loos
Date:
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

Re: pivot functions with variable number of columns

From
Misa Simic
Date:
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>
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




--
View this message in context: http://postgresql.1045698.n5.nabble.com/pivot-functions-with-variable-number-of-columns-tp5723013.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: pivot functions with variable number of columns

From
Pavel Stehule
Date:
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

> Kind Regards,
>
> Misa
>
> 2012/9/6 punnoose <punnoose.pj@dwisesolutions.com>
>>
>> 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
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/pivot-functions-with-variable-number-of-columns-tp5723013.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>


Re: pivot functions with variable number of columns

From
Misa Simic
Date:
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:
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

> Kind Regards,
>
> Misa
>
> 2012/9/6 punnoose <punnoose.pj@dwisesolutions.com>
>>
>> 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
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/pivot-functions-with-variable-number-of-columns-tp5723013.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>

Re: pivot functions with variable number of columns

From
Pavel Stehule
Date:
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


>
> 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:
>>
>> 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
>>
>> > Kind Regards,
>> >
>> > Misa
>> >
>> > 2012/9/6 punnoose <punnoose.pj@dwisesolutions.com>
>> >>
>> >> 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
>> >>
>> >>
>> >>
>> >>
>> >> --
>> >> View this message in context:
>> >>
>> >> http://postgresql.1045698.n5.nabble.com/pivot-functions-with-variable-number-of-columns-tp5723013.html
>> >> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>> >>
>> >>
>> >> --
>> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-general
>> >
>> >