Crosstab query on huge amount of values

From: Julia Jacobson <julia(dot)jacobson(at)arcor(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Crosstab query on huge amount of values
Date: 2011-01-16 22:40:34
Message-ID: 4D3373E2.1020805@arcor.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tomas Vondra 2011-01-16 22:41:39 Re: How to generate unique invoice numbers for each day
Previous Message Adrian Klaver 2011-01-16 22:32:19 Re: Why can't I change a password <SOLVED>