Thread: pivot functions with variable number of columns
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. Please do help me. Punnoose -- View this message in context: http://postgresql.1045698.n5.nabble.com/pivot-functions-with-variable-number-of-columns-tp5723013.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thu, Sep 6, 2012 at 10:44 AM, 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.
Please do help me.
Punnoose
Am not sure how your table structure is designed to use the best of crosstab. Here is a simple example to give some light on how to work with crosstab it.
Table & Data:
CREATE TABLE pivot_test (id integer, customer_id integer, product_code VARCHAR, quantity integer);
INSERT INTO pivot_test VALUES (1, 1, 'A', 10);
INSERT INTO pivot_test VALUES (2, 1, 'B', 20);
INSERT INTO pivot_test VALUES (3, 1, 'C', 30);
INSERT INTO pivot_test VALUES (4, 2, 'A', 40);
INSERT INTO pivot_test VALUES (5, 2, 'C', 50);
INSERT INTO pivot_test VALUES (6, 3, 'A', 60);
INSERT INTO pivot_test VALUES (7, 3, 'B', 70);
INSERT INTO pivot_test VALUES (8, 3, 'C', 80);
INSERT INTO pivot_test VALUES (9, 3, 'D', 90);
INSERT INTO pivot_test VALUES (10, 4, 'A', 100);
postgres=# select * from pivot_test;
id | customer_id | product_code | quantity
----+-------------+--------------+----------
1 | 1 | A | 10
2 | 1 | B | 20
3 | 1 | C | 30
4 | 2 | A | 40
5 | 2 | C | 50
6 | 3 | A | 60
7 | 3 | B | 70
8 | 3 | C | 80
9 | 3 | D | 90
10 | 4 | A | 100
(10 rows)
Here is Pivot kind result:
postgres=select * from crosstab
('select customer_id::text,
product_code::text,
quantity::text
from pivot_test
where product_code=''A'' or product_code=''B'' or product_code=''C''
order by 1,2'
) as ct(customer_id text, "A" text,"B" text,"C" text);
customer_id | A | B | C
-------------+-----+----+----
1 | 10 | 20 | 30
2 | 40 | 50 |
3 | 60 | 70 | 80
4 | 100 | |
(4 rows)
Someone, might have better example. Timely you can work with above example.
---
Regards,
Raghavendra
EnterpriseDB Corporation
On Wed, Sep 5, 2012 at 10:14 PM, punnoose <punnoose.pj@dwisesolutions.com> wrote:
Best Wishes,
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.
Best Wishes,
Chris travers
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
a very nice way is to use a cursor.
http://okbob.blogspot.cz/2008/08/using-cursors-for-generating-cross.html
HTH
WBL
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
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@wanadoo.fr> wrote:
Le jeudi 06 septembre 2012 à 00:40 -0700, Chris Travers a écrit :Below is an example in Perl : it selects the values in column>
>
> 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.
'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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
That is one of most wanted features of PostgreSQL, what is not solved yet,,,
But it seems will be soon with introductions of Stored Procedures...
For now, you must "know" what result (columns) you expects...
So the only one option for now is to use Dynamic SQL - to build your query dynamically based on data in your table (based on column what should be pivoted)..
And then execute that query...
You can use your client side language to build SQL or inisde DB you could make function what returns "text" as your Dynamic SQL and then execute it from your client...
Kind Regards,
Misa
2012/9/6 punnoose <punnoose.pj@dwisesolutions.com>
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.
Please do help me.
Punnoose
--
View this message in context: http://postgresql.1045698.n5.nabble.com/pivot-functions-with-variable-number-of-columns-tp5723013.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello 2012/9/6 Misa Simic <misa.simic@gmail.com>: > That is one of most wanted features of PostgreSQL, what is not solved yet,,, > > But it seems will be soon with introductions of Stored Procedures... > I wish :) > For now, you must "know" what result (columns) you expects... > > So the only one option for now is to use Dynamic SQL - to build your query > dynamically based on data in your table (based on column what should be > pivoted).. > > And then execute that query... > > You can use your client side language to build SQL or inisde DB you could > make function what returns "text" as your Dynamic SQL and then execute it > from your client... > there is a some workaround http://okbob.blogspot.cz/2008/08/using-cursors-for-generating-cross.html Pavel > Kind Regards, > > Misa > > 2012/9/6 punnoose <punnoose.pj@dwisesolutions.com> >> >> 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. >> Please do help me. >> Punnoose >> >> >> >> >> -- >> View this message in context: >> http://postgresql.1045698.n5.nabble.com/pivot-functions-with-variable-number-of-columns-tp5723013.html >> Sent from the PostgreSQL - general mailing list archive at Nabble.com. >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > >
Hi Pavel,
However, based on your name, and the name of the blog author :) I have made conlusion you are working on Stored Procedures things?
Hm... To me workaround looks as exactly as the same thing?
1) uses Dynamic SQL to bulid query (but returns refcursor insted of text)
2) client still needs to execute 2 commands (second is fetch instead of execute 'result')
I have a few questions about that... But will send another mail to dont mix subjects...
Cheers,
Misa
On Thursday, September 6, 2012, Pavel Stehule wrote:
On Thursday, September 6, 2012, Pavel Stehule wrote:
Hello
2012/9/6 Misa Simic <misa.simic@gmail.com>:
> That is one of most wanted features of PostgreSQL, what is not solved yet,,,
>
> But it seems will be soon with introductions of Stored Procedures...
>
I wish :)
> For now, you must "know" what result (columns) you expects...
>
> So the only one option for now is to use Dynamic SQL - to build your query
> dynamically based on data in your table (based on column what should be
> pivoted)..
>
> And then execute that query...
>
> You can use your client side language to build SQL or inisde DB you could
> make function what returns "text" as your Dynamic SQL and then execute it
> from your client...
>
there is a some workaround
http://okbob.blogspot.cz/2008/08/using-cursors-for-generating-cross.html
Pavel
> Kind Regards,
>
> Misa
>
> 2012/9/6 punnoose <punnoose.pj@dwisesolutions.com>
>>
>> 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.
>> Please do help me.
>> Punnoose
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/pivot-functions-with-variable-number-of-columns-tp5723013.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
2012/9/6 Misa Simic <misa.simic@gmail.com>: > Hi Pavel, > > Hm... To me workaround looks as exactly as the same thing? > > 1) uses Dynamic SQL to bulid query (but returns refcursor insted of text) > > 2) client still needs to execute 2 commands (second is fetch instead of > execute 'result') > > However, based on your name, and the name of the blog author :) I have made > conlusion you are working on Stored Procedures things? I worked on stored procedures - but not now - I have too much opened projects, and I would to finish it before - this claim is free now :) I am working (too slowly :( on PSM language). Regards Pavel > > I have a few questions about that... But will send another mail to dont mix > subjects... > > Cheers, > > Misa > > > On Thursday, September 6, 2012, Pavel Stehule wrote: >> >> Hello >> >> 2012/9/6 Misa Simic <misa.simic@gmail.com>: >> > That is one of most wanted features of PostgreSQL, what is not solved >> > yet,,, >> > >> > But it seems will be soon with introductions of Stored Procedures... >> > >> >> I wish :) >> >> > For now, you must "know" what result (columns) you expects... >> > >> > So the only one option for now is to use Dynamic SQL - to build your >> > query >> > dynamically based on data in your table (based on column what should be >> > pivoted).. >> > >> > And then execute that query... >> > >> > You can use your client side language to build SQL or inisde DB you >> > could >> > make function what returns "text" as your Dynamic SQL and then execute >> > it >> > from your client... >> > >> >> there is a some workaround >> >> http://okbob.blogspot.cz/2008/08/using-cursors-for-generating-cross.html >> >> Pavel >> >> > Kind Regards, >> > >> > Misa >> > >> > 2012/9/6 punnoose <punnoose.pj@dwisesolutions.com> >> >> >> >> 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. >> >> Please do help me. >> >> Punnoose >> >> >> >> >> >> >> >> >> >> -- >> >> View this message in context: >> >> >> >> http://postgresql.1045698.n5.nabble.com/pivot-functions-with-variable-number-of-columns-tp5723013.html >> >> Sent from the PostgreSQL - general mailing list archive at Nabble.com. >> >> >> >> >> >> -- >> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> >> To make changes to your subscription: >> >> http://www.postgresql.org/mailpref/pgsql-general >> > >> >