Author Topic: Concatenate as aggregation  (Read 158 times)

Offline hespora

  • Statesman
  • ******
  • Join Date: Nov 2015
  • Posts: 388
  • Forum Citizenship: +22/-0
Concatenate as aggregation
« on: 18 May 2020 06:08:32 am »
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]
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]
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?

Offline ashley

  • Full Member
  • ***
  • Join Date: Feb 2018
  • Posts: 17
  • Forum Citizenship: +3/-0
Re: Concatenate as aggregation
« Reply #1 on: 18 May 2020 07:36:30 am »
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/

Offline hespora

  • Statesman
  • ******
  • Join Date: Nov 2015
  • Posts: 388
  • Forum Citizenship: +22/-0
Re: Concatenate as aggregation
« Reply #2 on: 20 May 2020 05:55:24 am »
Thank you very much; I'll have a look at that and fiddle around with it!

 



       
Twittear