Caché SQL columns to list

Question

Do you know of any way to create the Result set below in a single SQL statement?

Record Set:

ID  AC9         Value
1   11111111    A
2   11111111    B
3   11111111    C
4   11111111    D
5   22222222    B
6   22222222    C
7   22222222    D
8   22222222    E
9   22222222    F
10  22222222    G

Result Set:

AC9         MyValue
11111111    A,B,C,D
22222222    B,C,D,E,F,G

Answer 1

Cache has a really easy way to pull this off using the list function.

select distinct ac9, list(value %FOREACH(ac9))

from (select 1 as id,       11111111 as ac9,  'A' as value
union all select 2,       11111111,        'B'
union all select 3,       11111111,        'C'
union all select 4,       11111111,        'D'
union all select 5,       22222222,        'B'
union all select 6,       22222222,        'C'
union all select 7,       22222222,        'D'
union all select 8,       22222222,        'E'
union all select 9,       22222222,        'F'
union all select 10,      22222222,        'G') sub