Re: Function to Pivot data - Mailing list pgsql-general

Andrew,

I know normalizing the database is the best for accessing and querying, it's
the maintainance and reporting that is my major concern.  The
reporting will be done in PHP to the web.  The PHP code will be written
by a student and the desire is to keep the code and database easy to
understand, easy to
learn and easy to maintain.

Actually, this database is just a single table right now.
The structure is:
    Title
    Source
    Date
    LastNameAuthor1
    FirstNameAuthor1
    LastNameAuthor2
    FirstNameAuthor2
    LastNameAuthor3
    FirstNameAuthor3
    Subject1
    Subject2
    Subject3
    Subject4
    Department

A few of the tables in a normalized database would be:
    Author:
    AuthorID, LastName, FirstName, DepartmentID

    Author_Book:
    AuthorID, BookID

    Book:
    BookID, Title, Date

I would want to be able to produce a report that contained the following
row structure:

    Title, Date, Author1, Author2, Author3, Author4

Where Author1, Author2, etc. are FirstName + LastName.

Thanks for the help.

Ellen
-----
At 10:49 AM 01/31/2002 -0500, you wrote:
>On Thu, Jan 31, 2002 at 09:17:35AM -0500, Ellen Cyran wrote:
>> I'm wondering if it is worth it to normalize data or not.
>> One problem with normalizing is that the data may require
>> pivoting.  Is there a pivot funtion available for postgresql?
>
>Postgres (and SQL) doesn't really have the idea of pivot tables.
>Really, what you are talking about is a data _presentation_ problem,
>and not a data _storage_ problem.
>
>The reason to normalise is that it gets you the most flexible data
>store.  If you have denormalised data, you find yourself tripping
>over the poor separation of the conceptual pieces.
>
>Tools that offer "pivot tables" are not really _databases_ (in that
>function), but report generators.  It's important to separate these
>things conceptually, so that you don't mess up your data storage with
>limiting considerations from what you want to do with it right now.
>
>You haven't offered an outline of the database schema or anything,
>here, so I can't suggest how you might go about getting the output
>you want.  But a quick bit of work in Perl might help.
>
>A
>
>--
>----
>Andrew Sullivan                               87 Mowat Avenue
>Liberty RMS                           Toronto, Ontario Canada
><andrew@libertyrms.info>                              M6K 3E3
>                                         +1 416 646 3304 x110
>
>