From: | "Igor Neyman" <ineyman(at)perceptron(dot)com> |
---|---|
To: | "Julia Jacobson" <julia(dot)jacobson(at)arcor(dot)de>, <pgsql-general(at)postgresql(dot)org> |
Cc: | "Steve Litt" <slitt(at)troubleshooters(dot)com> |
Subject: | Re: Crosstab query on huge amount of values |
Date: | 2011-01-17 20:02:14 |
Message-ID: | F4C27E77F7A33E4CA98C19A9DC6722A20701C088@EXCHANGE.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----Original Message-----
> From: Julia Jacobson [mailto:julia(dot)jacobson(at)arcor(dot)de]
> Sent: Monday, January 17, 2011 7:21 AM
> To: pgsql-general(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2011-01-17 20:05:46 | Re: help understanding collation order |
Previous Message | Вячеслав Блинников | 2011-01-17 20:00:52 | libpq: how to retrieve query result? |