Am 17.01.2011 00:20, schrieb Steve Litt:
On Sunday 16 January 2011 17:40:34 Julia Jacobson wrote:
Hello everybody out there using PostgreSQL,
A table with the results of students in different exams
student | date_of_exam | grade
------------------------------
Peter | 2010-09-09 | 2
Tom | 2010-09-09 | 1
Andy | 2010-09-21 | 3
Tom | 2010-09-21 | 4
Peter | 2010-09-21 | 1
Peter | 2010-10-11 | 2
shall be transformed to a denormalized view like:
student | grade_2010_09_09 | grade_2010_09_21 | grade_2010_10_11
----------------------------------------------------------------
Peter | 2 | 1 | 2
Tom | 1 | 4 | NULL
Andy | NULL | 3 | NULL
I've already done extensive Web-search and posted in Usenet for help
concerning this problem and was pointed to the tablefunc module which
seems to be a solution.
Since I only have a database but no administrative rights for the
PostgreSQL installation, I can't use the tablefunc module.
Is there any way to denormalize my table using a simple SQL script?
Thanks in advance,
Julia
Hi Julia,
If you're denormalizing it just for a report, you could do it in your
application, and just ringtoss rows onto the test periods.
If you want to have a permanent table containing the denormalized material
(and one would have to ask why), then one possible method would be the same as
for the report -- let your application ring toss rows onto the newly created
table containing an array. Since you have no administrative rights, the DBA
would need to create the denormalized table, and add another column every time
there's a new exam.
Let the darn thing run overnight, or perhaps do one exam at a time or a small
range of students at a time. Do you happen to know why they want a
denormalized table as opposed to just making an index sorted by student and
then by grade period? Do you have any idea how long it would take to create an
index sorted first by student and then by exam?
I'm sure there are easier ways of doing it, but what I suggested is one way
that it could work.
HTH
SteveT
Steve Litt
Recession Relief Package
http://www.recession-relief.US
Twitter: http://www.twitter.com/stevelitt
Hello Steve,
Thanks a lot for your answer.
Indeed, I actually want to denormalize my table for a report, but I need
to join the denormalized table with another table of the database for
this report.
So when I ring toss rows and columns in my application, it won't be
possible to do the join anymore.
Although I think PostgreSQL does good in not offering pivot tables like
Oracle or MS-SQL, I'm really desperately looking for a workaround here.
Regards,
Julia
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
