Re: Function to Pivot data

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(at)libertyrms(dot)info> M6K 3E3
> +1 416 646 3304 x110
>
>