pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcrosstab
6+ messages / 4 participants
[nested] [flat]
* crosstab
@ 2005-03-02 04:30 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
0 siblings, 1 reply; 6+ messages in thread
From: Theo Galanakis @ 2005-03-02 04:30 UTC (permalink / raw)
To: pgsql-sql
Hi I have looked into the tablefunc / crosstab contrib for postgres and it
appears like it can't perform what I need.
The crosstab function converts this :
row_name cat value
----------+-------+-------
row1 cat1 val1
row1 cat2 val2
row1 cat3 val3
row1 cat4 val4
row2 cat1 val5
row2 cat2 val6
row2 cat3 val7
row2 cat4 val8
To this :
row_name category_1 category_2
---------+------------+------------
row1 val1 val2
row2 val5 val6
Is it possible to do the opposite and go from a column(denormalized)
structure to a row(normalized) structure.
I have a table that is similarly stuctured like so:
Melbourne_figures Sydney_figures Adelaide_figures etc...
10 20 22
10 22 29
...
However I wish to convert like so :
Melbourne 20
Sydney 42
Adelaide 51
I have tried using unions or subselects however the table is quite large and
it takes far too long to run. The most efficient way would be to create a
stored proc that uses a cursor to loop through the table transforming the
data into the new table structure. However I would appreciate your feeback
before writing this procedure?
Theo
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: crosstab
@ 2005-03-02 04:47 Josh Berkus <josh@agliodbs.com>
parent: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2005-03-02 04:47 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-sql
THeo,
> I have tried using unions or subselects however the table is quite large
> and it takes far too long to run. The most efficient way would be to create
> a stored proc that uses a cursor to loop through the table transforming the
> data into the new table structure. However I would appreciate your feeback
> before writing this procedure?
A proc won't run any faster ... much slower, in fact. Unless you mean that
you want to use it to correct the actual table structure, which is what you
should be doing?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* crosstab
@ 2012-03-12 16:58 John Fabiani <johnf@jfcomputer.com>
0 siblings, 1 reply; 6+ messages in thread
From: John Fabiani @ 2012-03-12 16:58 UTC (permalink / raw)
To: pgsql-sql
Hi,
I don't know if it because I'm as sick as dog or I'm just a plain idiot - most
likely a little of both.
Here is my table
week_of date,
item_number text,
planned_demand integer.
I have
week_of item_number planned
2012-02-12 00005 200
2012-02-19 00005 -30
2012-02-26 00005 -16
I want to see
item_number 2012-02-12 2012-02-19 2012-02-26
00005 200 -30 -16
I actually have added fields but that should get me started.
Thanks for help from an old man with a very bad cold.
Johnf
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: crosstab
@ 2012-03-12 17:01 Pavel Stehule <pavel.stehule@gmail.com>
parent: John Fabiani <johnf@jfcomputer.com>
0 siblings, 1 reply; 6+ messages in thread
From: Pavel Stehule @ 2012-03-12 17:01 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
Hello
maybe this article helps
http://stackoverflow.com/questions/3002499/postgresql-crosstab-query
there are more ways
Regards
Pavel Stehule
2012/3/12 John Fabiani <johnf@jfcomputer.com>:
> Hi,
> I don't know if it because I'm as sick as dog or I'm just a plain idiot - most
> likely a little of both.
>
> Here is my table
>
> week_of date,
> item_number text,
> planned_demand integer.
>
> I have
> week_of item_number planned
> 2012-02-12 00005 200
> 2012-02-19 00005 -30
> 2012-02-26 00005 -16
>
> I want to see
>
> item_number 2012-02-12 2012-02-19 2012-02-26
> 00005 200 -30 -16
>
> I actually have added fields but that should get me started.
>
> Thanks for help from an old man with a very bad cold.
>
> Johnf
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: crosstab
@ 2012-03-12 18:07 John Fabiani <johnf@jfcomputer.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: John Fabiani @ 2012-03-12 18:07 UTC (permalink / raw)
To: pgsql-sql
select *
from crosstab('select item_number::text, week_of::date, planned_demand::text
from holding_table order by 1,2')
as ct(row_name text, week_of date, planned text)
The above does not work. What am I doing wrong?
Johnf
On Monday, March 12, 2012 06:01:21 PM Pavel Stehule wrote:
> Hello
>
> maybe this article helps
> http://stackoverflow.com/questions/3002499/postgresql-crosstab-query
>
> there are more ways
>
> Regards
>
> Pavel Stehule
>
> 2012/3/12 John Fabiani <johnf@jfcomputer.com>:
> > Hi,
> > I don't know if it because I'm as sick as dog or I'm just a plain idiot
> > - most likely a little of both.
> >
> > Here is my table
> >
> > week_of date,
> > item_number text,
> > planned_demand integer.
> >
> > I have
> > week_of item_number planned
> > 2012-02-12 00005 200
> > 2012-02-19 00005 -30
> > 2012-02-26 00005 -16
> >
> > I want to see
> >
> > item_number 2012-02-12 2012-02-19 2012-02-26
> > 00005 200 -30 -16
> >
> > I actually have added fields but that should get me started.
> >
> > Thanks for help from an old man with a very bad cold.
> >
> > Johnf
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: crosstab
@ 2012-03-12 18:11 Pavel Stehule <pavel.stehule@gmail.com>
parent: John Fabiani <johnf@jfcomputer.com>
0 siblings, 0 replies; 6+ messages in thread
From: Pavel Stehule @ 2012-03-12 18:11 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
2012/3/12 John Fabiani <johnf@jfcomputer.com>:
> select *
> from crosstab('select item_number::text, week_of::date, planned_demand::text
> from holding_table order by 1,2')
> as ct(row_name text, week_of date, planned text)
>
> The above does not work. What am I doing wrong?
what it does?
do you have tablefunc extension?
http://www.postgresql.org/docs/9.1/interactive/tablefunc.html
regards
Pavel Stehule
> Johnf
> On Monday, March 12, 2012 06:01:21 PM Pavel Stehule wrote:
>> Hello
>>
>> maybe this article helps
>> http://stackoverflow.com/questions/3002499/postgresql-crosstab-query
>>
>> there are more ways
>>
>> Regards
>>
>> Pavel Stehule
>>
>> 2012/3/12 John Fabiani <johnf@jfcomputer.com>:
>> > Hi,
>> > I don't know if it because I'm as sick as dog or I'm just a plain idiot
>> > - most likely a little of both.
>> >
>> > Here is my table
>> >
>> > week_of date,
>> > item_number text,
>> > planned_demand integer.
>> >
>> > I have
>> > week_of item_number planned
>> > 2012-02-12 00005 200
>> > 2012-02-19 00005 -30
>> > 2012-02-26 00005 -16
>> >
>> > I want to see
>> >
>> > item_number 2012-02-12 2012-02-19 2012-02-26
>> > 00005 200 -30 -16
>> >
>> > I actually have added fields but that should get me started.
>> >
>> > Thanks for help from an old man with a very bad cold.
>> >
>> > Johnf
>> >
>> > --
>> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-03-12 18:11 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-02 04:30 crosstab Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2005-03-02 04:47 ` Josh Berkus <josh@agliodbs.com>
2012-03-12 16:58 crosstab John Fabiani <johnf@jfcomputer.com>
2012-03-12 17:01 ` Pavel Stehule <pavel.stehule@gmail.com>
2012-03-12 18:07 ` John Fabiani <johnf@jfcomputer.com>
2012-03-12 18:11 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox