Can a MySQL query turn rows into columns? -

i have number of tables trying combine joins such, results returned in number of rows whereas have them generated new columns.

member_information table

memberid | firstname | lastname  ---------------------------------    1     |   john    |  harris    2     |   sarah   |  thompson    3     |   zack    |  lewis 

member_dependent_information table

memberid | firstname | lastname | type ---------------------------------------    1     |   amy     | harris   |  1     2     |   bryan   | thompson |  1    2     |   dewey   | thompson |  2    2     |   tom     | thompson |  2    3     |   harry   | lewis    |  2    3     |   minka   | lewis    |  1 

mysql query:

select     t1.firstname,     t1.lastname,     t1.memberid,     if(t2.type = '1',concat(t2.firstname,' ',t2.lastname),'') spouse_name,     if(t2.type = '2',concat(t2.firstname,' ',t2.lastname),'') child_name,      member_dependent_information t2 inner join     member_information t1 using     (memberid) order     t1.lastname asc,     t1.memberid asc; 

ideal results

memberid | firstname | lastname  | spouse_name    | child_name1   | child_name2  --------------------------------------------------------------------------------    1     |   john    |  harris   | amy harris     |     null       |   null        2     |   sarah   |  thompson | bryan thompson | dewey thompson | tom thompson        3     |   zack    |  lewis    | mika lewis     | harry lewis    |   null 

actual results

memberid | firstname | lastname  | spouse_name    | child_name  -------------------------------------------------------------------    1     |   john    |  harris   | amy harris     |    null    2     |   sarah   |  thompson | bryan thompson |    null      2     |   sarah   |  thompson |      null      | dewey thompson      2     |   sarah   |  thompson |      null      | tom thompson        3     |   zack    |  lewis    | mika lewis     |    null          3     |   zack    |  lewis    |      null      | harry lewis     

while query returns "correct" data in multiple rows, not combine result 1 single row needed.

the suggestion pivot tables / crosstabs has been mentioned below every reference able find suggests using mathematic calculations or number of fields returned known. not know information single member have 100 dependents (although more 4-8)

update #1

i feel getting closer final solution. added function group_concat query returns firstnames in single column , last names in single column still need break them out own individual columns.

new function is:

select   t1.memberid,   t1.firstname,   t1.lastname,   group_concat(t2.firstname) dep_firstnames,   group_concat(t2.lastname) dep_lastnames   member_information t1   left outer join member_dependent_information t2     on t1.memberid = t2.memberid   t1.status = 1 group   t1.memberid 

sometimes first step solving problem knowing it's called. after that, it's matter of googling. trying create called pivot table or crosstab report. here link explaining how create pivot tables in mysql. , here more in depth tutorial.

update:

now you've updated question, have clearer idea of trying accomplish. i'll give alternative solution similar not exactly want based on mysql's group_concat function.

select t1.firstname, t1.lastname, group_concat(concat(t2.firstname, ' ', t2.lastname)) member_information t1 left outer join member_dependent_information t2 on t2.memberid=t1.memberid group t1.memberid; 

i've verified query follows. first setup:

create table member_information (     memberid int unsigned auto_increment primary key,     firstname varchar(32) not null,     lastname varchar(32) not null ) engine=innodb;  create table member_dependent_information (     memberid int unsigned not null,     firstname varchar(32) not null,     lastname varchar(32) not null,     type int unsigned not null,     foreign key (memberid) references member_information(memberid) ) engine=innodb;  insert member_information (memberid, firstname, lastname) values (1, 'john', 'harris'), (2, 'sarah', 'thompson'), (3, 'zack', 'lewis');  insert member_dependent_information (memberid, firstname, lastname, `type`) values (1, 'amy', 'harris', 1), (2, 'bryan', 'thompson', 1), (2, 'dewey', 'thompson', 2), (2, 'tom', 'thompson', 2), (3, 'harry', 'lewis', 2), (3, 'minka', 'lewis', 1); 

and query , results:

mysql> select t1.firstname, t1.lastname, group_concat(concat(t2.firstname, ' ', t2.lastname))from member_information t1     -> left outer join member_dependent_information t2 on t2.memberid=t1.memberid     -> group t1.memberid; +-----------+----------+------------------------------------------------------+ | firstname | lastname | group_concat(concat(t2.firstname, ' ', t2.lastname)) | +-----------+----------+------------------------------------------------------+ | john      | harris   | amy harris                                           |  | sarah     | thompson | bryan thompson,dewey thompson,tom thompson           |  | zack      | lewis    | harry lewis,minka lewis                              |  +-----------+----------+------------------------------------------------------+ 3 rows in set (0.00 sec)