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.:
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.:
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?
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/
Thank you very much; I'll have a look at that and fiddle around with it!