Remapping/Concatenating in SQL

Question

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.

id  forename surname  address
1   John  These  Address1
2   Lucy  Values  Address1
3   Jenny  Are   Address1
4   John  All   Address2
5   Lucy  Totally  Address2
6   Jenny  Different Address2
7   Steve  And   Address2
8   Richard  Blah  Address2

address  John Lucy   Jenny  Steve  Richard
Address1     These Values   Are   (null)  (null)
Address2     All  Totally   Different And   Blah

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? I'm using MySQL if that makes a difference.

Answer 1

I'm not certain, but I think what you're trying to do is GROUP BY.

SELECT Address,Name FROM Table GROUP BY Name

if you want to select more columns, make sure they're included in the GROUP BY clause. Also, you can now do aggregate functions, like MAX() or COUNT().

Answer 2

I am not sure about the question, but from what I understand you can do:

SELECT concat(column1,column2,column3) as main_column, address from table;

Answer 3

Sorry, there was an image which doesn't seem to show up, you can see it at http://mi6.nu/remappedsql.png

I don't think a GROUP BY will do what I need.

Answer 4

Assuming that the column headings "john", "lucy" etc are fixed, you can group by the address field and use if() functions combined with aggregate operators to get your results:

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;

It is a bit brittle though.

There is also the group_concat function that can be used (within limits) to do something similar, but it will be ordered row-wise rather than column-wise as you appear to require.

eg.

select address, group_concat( concat( forename, surname ) ) tenants 
from tablename
group by address;

Answer 5

Martin, that's exactly what I needed. The first function works perfectly for my scenario and I can determine the field names programatically.

I knew there'd be a way to make the SQL pixies do my bidding.