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(dot)pj(at)dwisesolutions(dot)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