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