MySQL: Combine multiple row values into single column?

Question

Database: MySQL

Table: Teams

TeamID    INT
TeamName  VARCHAR(20)

Table: People

PeopleID   INT
FirstNameID INT
LastNameID  INT

Table: TeamMembers

TeamID  INT
PeopleID  INT

Table Example: Teams

TeamID       TeamName
1            Team Xstream
2            Team INsanity

Table Example: People

PeopleID   FirstNameID   LastNameID
1          1351          453
2          5463          763
3          976           8762
4          87            784
5          187           465
6          761           566
7          376           2134

Table Example: TeamMembers

TeamID       PeopleID
1            1
1            3
1            7
2            2
2            4
2            5
2            6

Desired Output:

TeamName       TeamMembers
Team Xstream   John Smith/Jane Doe/Daniel Davis
Team INsanity  Sally Sue/Tom Thomas/Jack Jones/Harry Henderson

There will not be a set number of TeamMembers per team, so it's not like I could have three subqueries because there will only be three team members. I've lightly looked online, but I always get the best and most thorough answers here. Any ideas or pointers, please let me know. I honestly have no idea where to begin here. Thanks.

Taryn · Accepted Answer · 2012-11-06 22:02:03Z

4

I am guessing that the names of the people are actually stored somewhere considering you are just showing ID numbers as names. But you will want to use both CONCAT() and GROUP_CONCAT() for this result. The first step, will join all of the tables and use the CONCAT() function:

select t.teamname,
 concat(p.FirstNameId, ' ', p.LastNameId) teamMembers
from teams t
left join teammembers m
  on t.teamid = m.teamid
left join people p
  on m.peopleid = p.peopleid;

See SQL Fiddle with Demo, which will produce the result:

|      TEAMNAME | TEAMMEMBERS |
-------------------------------
|  Team Xstream |    1351 453 |
|  Team Xstream |    976 8762 |
|  Team Xstream |    376 2134 |
| Team INsanity |    5463 763 |
| Team INsanity |      87 784 |
| Team INsanity |     187 465 |
| Team INsanity |     761 566 |

Once you have the data, then apply the GROUP_CONCAT() function and GROUP BY the teamname:

select t.teamname,
  group_concat(concat(p.FirstNameId, ' ', p.LastNameId) SEPARATOR '/') teamMembers
from teams t
left join teammembers m
  on t.teamid = m.teamid
left join people p
  on m.peopleid = p.peopleid
group by t.teamname;

See SQL Fiddle with Demo

results:

|      TEAMNAME |                     TEAMMEMBERS |
---------------------------------------------------
| Team INsanity | 761 566/87 784/187 465/5463 763 |
|  Team Xstream |      976 8762/376 2134/1351 453 |

answered Nov 6, 2012 at 22:02

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

XstreamINsanity Over a year ago

Wow, that was fast. Yes, you are correct that the people names are stored in another table. I may be trying to rationalize my database too much by doing so, but since there will be millions of records, I thought this might be best. It seems the Group_Concat will work. Thanks.

Taryn Over a year ago

@XstreamINsanity I am not sure the benefit you will have by storing the first/last name in a separate table. You will just have to perform additional joins, I suggest rethinking that design.

XstreamINsanity Over a year ago

Well in reality it's actually going to be a database for sports cards. There are many players that have names (first or last) that are the same. And example would be 'Anthony' or 'James', etc. So when creating the players table, instead of typing their names in, I wanted to use IDs from another table. There may be only minimal gain from it, but I'm willing to go with it for now.

Taryn Over a year ago

@XstreamINsanity Got it, well whatever will work for your situation. Glad the query works for you.

Mike Brant · Accepted Answer · 2012-11-06 22:09:34Z

You didn't provide information on the table where names are actually stored, but what you are looking for is GROUP_CONCAT function. This is how you would use it to show the first and last name id's. I will leave it to you to join the name table and replace the name id fields in the query with the actual name fields.

SELECT t.TeamName, GROUP_CONCAT(CONCAT(p.FirstNameID, ' ', p.LastNameID))
FROM Teams as t
INNER JOIN TeamMembers as tm on t.TeamID = tm.TeamID
INNER JOIN People as p on tm.PeopleID = p.PeopleID
GROUP BY t.TeamName

By the way, normalizing the names out into their own table and using nameID seems like probably a good example of over normalization. Why not just have name values in People table? This will save you adding a fourth table to your query (and possibly fifth table if first and last names are in different tables).

Collectives™ on Stack Overflow

MySQL: Combine multiple row values into single column?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related