In case anyone is interested, I've corrected the previously faulty code as follows. I also tried to make it a little bit more readable.
SELECT a, b, stuff(
(
SELECT ', ' + CAST(level1 AS VARCHAR(8000))
FROM (
SELECT a, b, c + '(' + stuff(
(
SELECT ', ' + CAST(d AS VARCHAR(8000))
FROM #example
WHERE a = x.a
AND b = x.b
AND c = x.c
FOR XML path('')
) + ')'
, 1, 1, '') AS level1
FROM #example AS x
GROUP BY a, b, c
) y
WHERE a = z.a
AND b = z.b
FOR XML path('')
)
, 1, 1, '') AS level2
FROM (
SELECT a, b
FROM #example
GROUP BY a, b
) z
;
The only reason why I can see to avoid a CTE is if the real goal is to make it run in something like MySQL. So much for the myth of portability. 😉