Re: Crosstab query on huge amount of values

> -----Original Message-----
> From: Julia Jacobson [mailto:julia.jacobson@arcor.de]
> Sent: Monday, January 17, 2011 7:21 AM
> To: pgsql-general@postgresql.org
> 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