Crosstab query on huge amount of values

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

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

-----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

On Monday 17 January 2011 07:21:11 you wrote:

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

Hi Julia,

I liked Igor Neyman's suggestion and hope it fills the bill, but if that
doesn't work for you, instead of setting up a join you could just have your
app do lookups on the second table for each student/exam combination, or for
each student.

I don't know what your report needs to do, but if part of the problem is you
need a student total in the student's page header instead of footer, you could
do a 2 pass thing where pass 1 is

for each student
look up corresponding row in other table
for each exam
look up corresponding row in other table
write exam temp table entry
update student totals
end
write student header temp table entry
write student footer temp table entry
end

Now here's the thing. The temp table is sorted by student, then
line_type_flag, then exam. That flag is set by the various writes such that
the flag for a student header sorts above the flag for the exam entries, which
sorts above the student footer.

Then you just iterate right down the temp table, in student/flag/exam order,
and it maps right into your report.

Like I say, I don't know if that was your problem domain, but I've used it
quite a bit when my header needed to "read the tealeaves" to know what would
only be known after the whole entity had been read and calculated.

SteveT

Steve Litt
Recession Relief Package
http://www.recession-relief.US
Twitter: http://www.twitter.com/stevelitt