Re: Function to Pivot data

On Fri, Feb 01, 2002 at 10:42:24AM -0500, Andrew Sullivan wrote:
> 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

does it have to be the result of a sql select?

how about reswizzling --

    create table book (
        id serial,
        title varchar(80),
        isbn  varchar(10),
        ...
    );
    create table author (
        id serial,
        book_id int references book( id ),
        lname varchar(50),
        ...
    );
    ...

    insert into book(title)
        values('Foundation and Empire');
    insert into author(book_id,lname)
        values(currval('book_id_seq'),'Asimov');

    insert into book(title)
        values('The Ugly Little Boy');
    insert into author(book_id,lname)
        values(currval('book_id_seq'),'Asimov');
    insert into author(book_id,lname)
        values(currval('book_id_seq'),'Silverberg');

then

    select
        b.title,
        a.lname
    from
        book b,
        author a
    where
        b.isbn = "$1"
        and
        a.book_id = b.id
    order by
        a.id
    ;

sounds like a job for the middleware to assemble the output...?

    $auth = $dbh->selectall_arrayref(
        $sql_from_above
    );
    my $ix = 0;
    my %fld = (
        title => $auth->[0][0],
        map {$ix++; "author$ix" => $_->[1]} @$auth
    );
    ...

--
DEBIAN NEWBIE TIP #104 from Sean Quinlan <smq@gmx.co.uk>
:
Looking to CUSTOMIZE THE COLORS USED BY LS?  I find its easier
to run "dircolors -p >~/.dircolors" and then add "eval
`dircolors -b ~/.dircolors`" to my .bashrc and then make all
changes to ~/.dircolors (instead of the system-wide
/etc/DIR_COLORS).  Probably more pertinent on a multi user
system, but good policy nevertheless.

Also see http://newbieDoc.sourceForge.net/ ...