listduckdb

duckdb - aggregate string with a given separator

The standard aggregator makes coma separated list:

$ SELECT list_string_agg([1, 2, 'sdsd'])
'1,2,sdsd'

How can I make a smicolumn separated list or '/'-separated? Like '1;2;sdsd' or '1/2/sdsd'.

Solution

  • I believe string_agg function is what you want which also supports "distinct".

    # Python example
import duckdb as dd

CURR_QUERY = \
'''
SELECT string_agg(distinct a.c, ' || ') AS str_con
FROM (SELECT 'string 1' AS c
      UNION ALL
      SELECT 'string 2' AS c, 
      UNION ALL
      SELECT 'string 1' AS c) AS a
'''
print(dd.query(CURR_QUERY))

    Above will give you "string 1||string 2"