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
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