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(at)gmx(dot)co(dot)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/ ...