Equivalent of MSSQL's STUFF and XML PATH in MYSQL

Question

I have an mssql database that I want to convert/migrate to mysql.

I am getting the error message below.

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') FROM (SELECT a3t' at line 10

SELECT DISTINCT task_id, members = STUFF(( SELECT N', ' + t.Ful FROM (SELECT teamAlpha.task_id, ( users.firstname   + ' ' + users.lastname ) as Ful FROM teamAlpha JOIN users ON users.user_id = teamAlpha.user_id ) t WHERE t.task_id = u.task_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') FROM (SELECT teamAlpha.task_id, ( users.firstname + ' ' + users.lastname ) as Ful FROM teamAlpha JOIN users ON users.user_id = teamAlpha.user_id ) u      

Here's the code:

    $query = $this->db->query("
 SELECT DISTINCT
 task_id,
 members = STUFF((
      SELECT N', ' + t.Ful
      FROM (SELECT teamAlpha.task_id,
                     ( users.firstname  + ' ' + users.lastname ) as Ful
                FROM  teamAlpha
                JOIN users ON users.user_id = teamAlpha.user_id ) t
      WHERE t.task_id = u.task_id   
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM 
(SELECT teamAlpha.task_id,
         ( users.firstname  + ' ' + users.lastname ) as Ful
    FROM  teamAlpha
    JOIN users ON users.user_id = teamAlpha.user_id ) u");

    return $query->result();

What is the equivalent of mssql's STUFF() and XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '' in mysql? Thank you very much for the help.

Answer 1

It looks like you just want to generate a CSV list of people associated with a given task across your table. You may try the following query:

SELECT
    ta.task_id,
    GROUP_CONCAT(u.firstname, ' ', u.lastname) AS fullname
FROM teamAlpha ta
INNER JOIN users u
    ON u.user_id = ta.user_id
GROUP BY
    ta.user_id;

Note the ugliness of having to use FOR XML PATH disappears. Though SQL Server has rich support for analytic functions, it's group concatenation support was not so good. In SQL Server 2017 there is a new function STRING_AGG which basically does the same thing as MySQL's GROUP_CONCAT.