Re: Function to Pivot data

Ellen Cyran <ellen@urban.csuohio.edu> writes:
> 2.  I can't be sure what the maximum number of authors is either.  I could
> of course make the maximum pretty large, but then it does become
> somewhat tedious to code the SQL statement.  Could this be easily made into a
One way that would work is

select
    title,
    (select b.author from tbl_author b, author_book c
     where a.bookID = c.bookID and b.authorID = c.authorID
     and c.auth_rank = 1) as auth1,
    (select b.author from tbl_author b, author_book c
     where a.bookID = c.bookID and b.authorID = c.authorID
     and c.auth_rank = 2) as auth2,
    (select b.author from tbl_author b, author_book c
     where a.bookID = c.bookID and b.authorID = c.authorID
     and c.auth_rank = 3) as auth3,
    -- repeat until bored
from book a;

This is pretty grotty however: it's both verbose and inefficient since
each subselect gets evaluated independently.  What I think I'd really do
is join the authors to author_book just once using a temp table:

create temp table author_match as
  select bookID, author, auth_rank
    from tbl_author b, author_book c
    where b.authorID = c.authorID;

create index author_match_index on author_match (bookID, auth_rank);

Then

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
from book a;

With the index, this should run tolerably fast.

            regards, tom lane