Re: Function to Pivot data

On Tue, Feb 12, 2002 at 11:49:26AM -0500,
Andrew Sullivan <andrew(at)libertyrms(dot)info> wrote:
> On Mon, Feb 11, 2002 at 07:39:38AM -0600, Bruno Wolff III wrote:
>
> > The way I did this for a tiny book database I have set up for my wife
> > to keep track of books is to have an edition table, an author table and
> > a table of edition author pairs. It isn't ordered, but it could be
> > by adding another field to the edition, author pairs.
>
> That was my original suggestion. But then, how do you make sure that
> every edition has only one first author, only one second, &c.? Also,
> you can't have a generic query which gets the authors for every book,
> and shows them in the tabular output that was originally desired
> (hence the pivot table). You could, however, write some code outside
> the database which would first query the book_author table, figure
> out how many authors were necessary, and then build the real query
> that way.

I did have another suggestion in there about using a third column on
the author - book records to use for ordering. If you use something
like that you could write general queries using order by to get things
in author order.