> -----Original Message----- > From: Julia Jacobson [mailto:julia.jacobson@xxxxxxxx] > Sent: Monday, January 17, 2011 7:21 AM > To: pgsql-general@xxxxxxxxxxxxxx > Cc: Steve Litt > Subject: Re: Crosstab query on huge amount of values > > 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 Write a PlPgSQL function, that will re-arrange your rows into columns and store the results in temporary table. Then you can join this temp table with the other table for your report. Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
