I have two tables:
people (id,first,last)
and phones (id, type, phone)
...
What I would like to do, is 'coalesce' the phones to produce another table:
peopleandphones (id,first,last,home,cel,work,fax)
So I could make a much nicer report. But I cannot figure out how to do this!
Raw SQL doesn't really do that well. The ideal way is to have a program on the client designed for this sort of thing. It's usually called a matrix report or cross-tabulation. Commercial packages that do this include Oracle Reports, Crystal Reports, and Microsoft Access. But for something free that you can use with SQLite, Jasper Reports might do it (not sure).
Otherwise, you can do it in this limited situation with a kludgy query, something like:
select people.id,
people.first,
people.last,
home.phone as home,
cell.phone as cell,
work.phone as work,
fax .phone as fax
from people
left join phones as home (people.id = home.id)
left join phones as work (people.id = work.id)
left join phones as cell (people.id = cell.id)
left join phones as fax (people.id = fax .id);...but you'll have to add to the query every time you add a phone type, which is why I call it a kludge.
Good luck.
Scott Lamb
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
