Remapping fields

[Echilon]

I'm trying to reorder/group a set of results using SQL. I have a few fields (which for the example have been renamed to something a bit less specific), and each logical group of records has a field which remains constant - the address field. There are also fields which are present for each address, these are the same for every address.

This image helps to clarify the situation -

http://mi6.nu/remappedsql.png

For example: John,Lucy,Jenny,Steve and Richard are the only possible names at each address. I know this because it's stored in another location.

Can I select values from the actual records in the left hand image, and return them as a result set like the one on the right?

 

[AdaHacker]

I was a little fuzzy on your description, but from the image, it looks like you're trying to do a cross tab kind of a thing.

The good news is that it is possible to accomplish this sort of thing in SQL. The bad news is that there's no simple or obvious way to do it. The queries I've seen for this were fairly long, complicated, and only worked for a fixed set of columns. Here's a few examples to give you an idea to the general method:
Creating Crosstabs in MySQL
MySQL Wizardry
The Wizard revisited: Dynamic Crosstabs using MySQL Stored Procedures
You may be able to adapt those to what you're trying to do. If you need to dynamically determine the number of columns in the result set, the dynamic SQL method could be useful.

On the other hand, depending on what you're actually doing and how comfortable you are with SQL, it might just be easier to do this in procedural code. Or maybe even dump the data and leta spreadsheet tabluate it. SQL isn't exactly optimized for this sort of thing.

 

[Echilon]

That's exactly what I needed. It works using:
SELECT
MAX(IF(forename='john',surname,null)) AS john, MAX(IF(forename='lucy',surname,null)) AS lucy, MAX(IF(forename='jenny',surname,null)) AS jenny,
MAX(IF(forename='steve',surname,null)) AS steve,
MAX(IF(forename='richard',surname,null)) as richard, address FROM tablename GROUP BY address;

Thanks for the help.