Concatenate as aggregation

[hespora]

Situation: Relational DB, I have a simple query with 2 Dimensions and no facts. Dim1 is unique, whereas Dim2 can have the same value multiple times, e.g.:

Code Select Expand

Dim1   Dim2
A      X
B      X
C      X
D      Y
E      Y
F      Z

what I now need is a way to return a single query row per value for Dim2, with a concatenation of the relevant values for Dim1, e.g.:

Code Select Expand

Dim1   Dim2
A,B,C  X
D,E    Y
F      Z

I know how to do this *in layout* using repeaters, but this time, I need it in a query, as I need to join that query to another. Does anyone have any idea on how to achieve this?

[ashley]

I haven't test this in a list, but CognosPaul has an article on "listagg" which concatenates text together using a db2 function. It might be possible to user other db functions instead if you are not on db2?

http://cognospaul.com/2014/10/20/quickie-aggregating-text-cognos-crosstab/

[hespora]

Thank you very much; I'll have a look at that and fiddle around with it!