How to bring different values from results column into single row by value from other column sql-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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.