CODEWITHSUNDEEP
sqlintersystems-cache
Chad Johnson
Chad Johnson

Reputation: 65

Caché SQL columns to list

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

Upvotes: 3

Views: 515

Answers (2)

DdP
DdP

Reputation: 438

One can also make use of a GROUP BY statement instead of the %FOREACH function inside the LIST() aggregate:

SELECT AC9, LIST(Value) As MyValue FROM Source.Table GROUP BY AC9

While LIST() isn't standard, I think the overall structure of the query is a bit easier to understand due to the aggregate function + GROUP BY pairing.

Upvotes: 2

Niederee
Niederee

Reputation: 4295

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

Upvotes: 5

Related Questions