Can a MySQL query turn rows into columns?

mysql

Sometimes the first step to solving your problem is knowing what it's called. After that, it's simply a matter of googling. 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 in depth tutorial.

UPDATE:

Now that you've updated the question, I have a clearer idea of what you are trying to accomplish. I'll give you an alternative solution which is similar but not exactly what you want based on MySQL's 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've verified this query as follows. First the 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 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)

I'm not sure how the OP transformed from having programID and Status to how it is now, but the closest thing I would be able to get is (which does not 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;

Which 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 that Children_names into separate columns.

Can a MySQL query turn rows into columns?

UPDATE:

Related

Recent Posts