string_agg.rb

lib/sequel/extensions/string_agg.rb
Last Update: 2017-08-01 08:12:01 -0700

The string_agg extension adds the ability to perform database-independent aggregate string concatentation. For example, with a table like:

c1 | c2
---+---
a  | 1
a  | 2
a  | 3
b  | 4

You can return a result set like:

c1 | c2s
---+---
a  | 1,2,3
b  | 4

First, you need to load the extension into the database:

DB.extension :string_agg

Then you can use the Sequel.string_agg method to return a Sequel expression:

sa = Sequel.string_agg(:column_name)
# or:
sa = Sequel.string_agg(:column_name, '-') # custom separator

You can specify the order in which the concatention happens by calling order on the expression:

sa = Sequel.string_agg(:column_name).order(:other_column)

Additionally, if you want to have the concatenation only operate on distinct values, you can call distinct:

sa = Sequel.string_agg(:column_name).order(:other_column).distinct

These expressions can be used in your datasets, or anywhere else that Sequel expressions are allowed:

DB[:table].
  select_group(:c1).
  select_append(Sequel.string_agg(:c2))

This extension currenly supports the following databases:

  • PostgreSQL 9+

  • SQLAnywhere 12+

  • Oracle 11g+ (except distinct)

  • DB2 9.7+ (except distinct)

  • MySQL

  • HSQLDB

  • H2

Related module: Sequel::SQL::StringAgg