Search Postgresql Archives

Re: pivot functions with variable number of columns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx> 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 = "">
$$ 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux