Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Concatenating rows in a table

  • 14 July 2020
  • 3 replies
  • 1466 views
Harry_Morris

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

er1k
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

Brett_Sauve
Rank
Userlevel 3

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

R

@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


Terms & Conditions