Can a MySQL query convert rows to columns?

Question

I have several tables that I am trying to combine with joins, but as such, the results are returned in a series of rows, while I would like them to be generated as 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),'') AS Spouse_Name, IF(t2.Type = '2',CONCAT(t2.FirstName,' ',t2.LastName),'') AS Child_Name, FROM member_dependent_information t2 INNER JOIN member_information t1 USING (MemberID) ORDER BY t1.LastName ASC, t1.MemberID ASC; 

Perfect 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 

As long as my query returns the "correct" data in several rows, it does not combine the result in one row if necessary.

The following is a suggestion for pivot tables / crosstabs, but every link I can find suggests using math calculations or the number of fields returned is known. I will not know this information, since one member MAY have up to 100 dependents (although more than 4-8)

UPDATE # 1

I feel I'm getting closer to the final decision. I added the GROUP_CONCAT function to my query, which returns ALL the first names in one column and ALL the last names in one column, but you still need to split them into separate columns.

New Feature:

 SELECT t1.MemberID, t1.FirstName, t1.LastName, GROUP_CONCAT(t2.FirstName) AS Dep_Firstnames, GROUP_CONCAT(t2.LastName) AS Dep_LastNames FROM member_information t1 LEFT OUTER JOIN member_dependent_information t2 ON t1.MemberID = t2.MemberID WHERE t1.Status = 1 GROUP BY t1.MemberID 

Answer 1

Sometimes the first step to solving your problem is to know what he called. After that, it's just a search question. What you are trying to create is called a pivot table or crosstab report . Here is a link explaining how to create pivot tables in MySQL . And here is a more detailed tutorial .

UPDATE:

Now that you have updated the question, I have a clearer idea of ​​what you are trying to accomplish. I will give you an alternative solution that looks like, but not quite what you want, based on the MySQL GROUP_CONCAT function.

 select t1.FirstName, t1.LastName, group_concat(concat(t2.FirstName, ' ', t2.LastName)) from member_information as t1 left outer join member_dependent_information as t2 on t2.MemberID=t1.MemberID group by t1.MemberID; 

I checked this query as follows. First install:

 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 into member_information (MemberID, FirstName, LastName) values (1, 'John', 'Harris'), (2, 'Sarah', 'Thompson'), (3, 'Zack', 'Lewis'); insert into 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 now the query and results:

 mysql> select t1.FirstName, t1.LastName, group_concat(concat(t2.FirstName, ' ', t2.LastName))from member_information as t1 -> left outer join member_dependent_information as t2 on t2.MemberID=t1.MemberID -> group by 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) 

Answer 2

I'm not sure how the OP has evolved from programID and Status to how it is now, but the closest thing I could get (which doesn't require pivot tables):

 SELECT t1.MemberID, t1.FirstName, t1.LastName, concat(t2.FirstName, ' ', t2.LastName) as Spouse_Name, group_concat(concat(t3.FirstName, ' ', t3.LastName) ORDER BY t3.FirstName) as Children_names FROM member_information t1 LEFT JOIN member_dependent_information t2 ON (t1.MemberID=t2.MemberID AND t2.Type=1) LEFT JOIN member_dependent_information t3 ON (t1.MemberID=t3.MemberID and t3.Type=2) GROUP BY MemberID; 

What produces:

  + ---------- + ----------- + ---------- + --------------- - + ----------------------------- +
 |  MemberID |  FirstName |  Lastname |  Spouse_Name |  Children_names |
 + ---------- + ----------- + ---------- + --------------- - + ----------------------------- +
 |  1 |  John |  Harris |  Amy Harris |  NULL |
 |  2 |  Sarah |  Thompson |  Bryan Thompson |  Dewey Thompson, Tom Thompson |
 |  3 |  Zack |  Lewis |  Minka Lewis |  Harry Lewis |
 + ---------- + ----------- + ---------- + --------------- - + ----------------------------- + 

and it would be "easy" with any programming language to extract these Children_names into separate columns.

Answer 3

This is not exactly what you are looking for, but it may be a step in the right direction.

 SELECT Table1.memberIDs, Table1.firstname, Table1.lastnames, Table2.programIDs, Table3.description FROM Table1, Table2, Table3 WHERE Table1.memberIDs = Table2.memberIDs AND Table2.programIDs = Table3.programID