Remapping/Concatenating in SQL

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

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;

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

SELECT Address,Name FROM Table GROUP BY Name

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