Question 113016 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to bring different values from results column into single row by value from other column sql

I have the following tables:

Teammate ID Teammate name Team id Teams
1 Amy 11 Sales
1 Amy 12 Support
1 Amy 13 Marketing
2 Peter 12 Support
2 Peter 13 Marketing

And I want to group my results so the Teams column appears in one single row by Teammate Id or Teammate name as per below:

Teammate ID Teammate name Team id Teams
1 Amy 11, 12, 13 Sales, Support, Marketing
2 Peter 12, 13 Support, Marketing

Which function would be best/cleanest to use for this purpose? I tried subqueries, coalescing, some weird XML path thing but as a new SQL user I can’t wrap my head around figuring this one out

My original query which gave me the results is;

   SELECT
    tm.teammate_id AS "Teammate ID",
    tm.name AS "Teammate name",
    
    itt.team_id AS "Team IDs",
    
    it.team AS "Teams"
    
    
FROM
    intercom_teammates AS tm

LEFT JOIN intercom_teammate_teams AS itt 
ON tm.teammate_id = itt.teammate_id

LEFT JOIN intercom_teams AS it 
ON tm.teammate_id = itt.teammate_id
Tags:

2

Answers


  1. 0

    A simple group_concat would do the trick:

    select it.TeammateID,
       group_concat( distinct it.Teammatename SEPARATOR ',') as Teammatename,
       group_concat( it.Teamid SEPARATOR ',') as Teamid,
       group_concat( it.Teams SEPARATOR ',') as Teams
from intercom_teammates it
group by it.TeammateID ;

    https://dbfiddle.uk/rU8-h8rX

    Note. I used distinct on Teammatename, but I think it is excess and you can remove it if for every different TeammateID the Teammatename is unique.

    Login or Signup to reply.
  2. 0 
    SELECT
    tm.teammate_id AS "Teammate ID",
    tm.name AS "Teammate name",
    GROUP_CONCAT(DISTINCT itt.team_id ORDER BY itt.team_id ASC) AS "Team IDs",
    GROUP_CONCAT(DISTINCT it.team ORDER BY it.team DESC) AS "Teams"
FROM intercom_teammates tm
LEFT JOIN intercom_teammate_teams itt ON tm.teammate_id = itt.teammate_id
LEFT JOIN intercom_teams it ON tm.teammate_id = it.teammate_id
GROUP BY tm.teammate_id, tm.name

    Note: I’ve corrected your JOIN to intercom_teams changing itt.teammate_id to it.teammate_id; it was throwing off the result set.

    Result:

    | Teammate ID | Teammate name | Team IDs | Teams                    |
|-------------|---------------|----------|--------------------------|
| 1           | Amy           | 11,12,13 | Support,Sales,Marketing  |
| 2           | Peter         | 12,13    | Support,Marketing        |

    Fiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search