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. |
