Re: Function to Pivot data

On Thu, Jan 31, 2002 at 05:35:26PM -0500, Tom Lane wrote:
>
> select
>     a.title,
>     (select author from author_match am
>      where am.bookID = a.bookID and auth_rank = 1) as auth1,
>     (select author from author_match am
>      where am.bookID = a.bookID and auth_rank = 2) as auth2,
>     (select author from author_match am
>      where am.bookID = a.bookID and auth_rank = 3) as auth3,
>     -- repeat until bored
           ^^^^^^^^^^^^^^^^^^

This is the real problem: for any given book, you can't know in
advance how many authors it might have.  It's why I sort of thought
that a simple lookup table approach wouldn't be a good answer for
this: you have an ordered data set of unpredictable size for every
item in the book table.

Maybe the answer is to use an array in the lookup table.  That way
you can order the author entries the way you want, and still look
them up.  I haven't worked with arrays in Postgres, though, so I
don't know if this strategy will work well.  It's certainly not as
simple as the original outline supposed; but if you want to catalogue
actual books, a simple model won't work.  (If you doubt me, have a
read of the MARC standard!)

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110