Re: Function to Pivot data

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ellen Cyran <ellen(at)urban(dot)csuohio(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Function to Pivot data
Date: 2002-01-31 22:35:26
Message-ID: 5902.1012516526@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sharon Cowling 2002-01-31 22:42:00 Authorization in a function
Previous Message Darren Ferguson 2002-01-31 22:24:11 Re: Function to Pivot data