[thelist] MySQL: from lookup tables to flat table

Rickards, Julian (NDM) julian.rickards at ndm.gov.on.ca
Thu Apr 20 09:07:28 CDT 2006


Take a situation like this where a person has a personID, a name and in another table, types of phone numbers (home, work, cell,...). If a person has multiple phones, the best you can do with a SQL query would be:

personID, name, phoneType
001, john, home
001, john, work
001, john, cell
002, jane, home
002, jane, work
003, tom, home
004, julie, cell

etc.

Then you may use another programming language to produce:

001, john, home, work, cell
002, jane, home, work
003, tom, home
004, julie, cell

-----Original Message-----

> What kind of query would give a table like this:
> personId, name, [field1, field2, ...], [type1, type2, ...]
> 
> i.e. field and type columns are comma separated lists which can also
> be empty.



More information about the thelist mailing list