groupconcat.sql

CREATE TABLE test
    ("id" int PRIMARY KEY, "title" varchar(5), "cat" varchar(2));
    
INSERT INTO test
    ("id", "title", "cat")
VALUES
    (1, 'one', 'en'),
    (2, 'two', 'en'),
    (3, 'three', 'en'),
    (4, 'vier', 'de'),
    (5, 'fuenf', 'de'),
    (6, 'sechs', 'de')
;

--see https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation

select stuff((select ',' + title from test where cat = "outer".cat for xml path(''), type).value('.[1]', 'varchar(max)'), 1, 1, '')
from (select title, cat from test) "outer"
group by cat

--need distinct if no group by present
select distinct stuff((select ',' + title from test for xml path(''), type).value('.[1]', 'varchar(max)'), 1, 1, '')
from test "outer"

--mysql
--select group_concat(title, ',')
--from test
--group by cat

;with cte(expr, rank) as
(select title, DENSE_RANK() OVER (ORDER BY cat) from test)
select stuff((select ',' + expr from cte where rank = "outer".rank for xml path(''), type).value('.[1]', 'varchar(max)'), 1, 1, '')
from cte "outer"
group by rank

SqlserverDialectTrait.php

<?php
   /**
     * Transforms the query as necessary if it contains a GROUP_CONCAT() expression.
     *
     * @param \Cake\Database\Query $original The query to be transformed
     * @return \Cake\Database\Query
     */
    protected function _transformGroupConcat($original)
    {
        $groupConcats = [];
        foreach ($original->clause('select') as $expr) {
            if ($expr instanceof FunctionExpression && $expr->name() === 'GROUP_CONCAT') {
                $expr //reduce $expr no a no-op
                    ->name('')
                    ->iterateParts(function ($p, $key) {
                        return $key === 0 ? $p : null;
                    });
                $groupConcats[] = $expr;
            }
        }

        $inner = clone $original;
        $inner
            ->select($original->clause('group'))
            ->group([], true);3

        if (count($original->clause('group')) == 0) {
            $original->distinct();
        }
        
        $original->from(['_cake_subquery' => $inner], true);
        
        foreach($groupConcats as $expr) {
            $subquery = clone $original;
            $subquery
                ->select()
                ->epilog("FOR XML PATH(''), type"); //TODO...
        }
    }