Re: pivot functions with variable number of columns

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(dot)lists(at)wanadoo(dot)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(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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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