Re: Function to Pivot data

Ellen Cyran <ellen(at)urban(dot)csuohio(dot)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