Обсуждение: pivot functions with variable number of columns

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.

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

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
>
>

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
>> >
>> >