The query below will concatenate (in alphabetic order) the fields in column "v", grouped by the column "g".
This can be seen as a kind of "table transpose", or as a new grouping (or aggregation) function, next to COUNT, SUM, AVG, MIN and MAX.
Just to help understand the query: the table
Code:
items.g items.v
10 a
10 b
20 b
20 c
20 d
will be transformed into
Code:
g list
10 a, b
20 b, c, d
Code:
WITH t(g, list) AS
( SELECT g, CAST(RTRIM(v) AS VARCHAR(254))
FROM items
UNION ALL
SELECT items.g, t.list || ', ' ||RTRIM(items.v)
FROM t INNER JOIN items ON t.g = items.g
WHERE LOCATE(RTRIM(items.v),t.list) = 0 )
, l(g, len) AS
( SELECT g, MAX(LENGTH(list))
FROM t
GROUP BY g )
SELECT t.g, MIN(t.list)
FROM t INNER JOIN l ON l.g = t.g AND l.len = LENGTH(t.list)
GROUP BY t.g