Re: pivot functions with variable number of columns

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