Question

Concatenating rows in a table

  • 14 July 2020
  • 3 replies
  • 1466 views

I am looking for a way to concatenate a group of string which all appear in the same column, however in different rows.


ex.



  1. bob

  2. joe

  3. harry

  4. tom


Would concatenate to bob, joe, harry, tom


Is this possible?


3 replies

Userlevel 5
Badge

If your database supports LISTAGG() then you can use LISTAGG(name,’, ') to get list of names separated by comma.


More here: https://looker.com/blog/how-to-use-listagg-distinct-amazon-redshift-looker

Userlevel 3

The type: list measure (docs here) is meant for this purpose.

@Brett_Sauve is there a way to change the order by clause of the listagg ?
a general syntax or listagg would be 
LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] ) [ WITHIN GROUP ( <orderby_clause> ) ]
How can we have multiple objects in the order by clause ?
ex: listagg(distinct hire_date, ‘, ’) within group (order by emp_id, dept_id)

Reply