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