DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Simulating MySQL's Group_concat Aggregate Function In SQLite3 (using Ruby)

05.13.2005
| 10565 views |
  • submit to reddit
        You can simulate the MySQL group_concat aggregate function using the SQLite/Ruby create_aggregate method (documented here
http://docs.jamisbuck.org/read/chapter/17#page77 ), as follows:

require 'sqlite3'

db = SQLite3::Database.open("test.db")

db.create_aggregate("group_concat", 1) do
  step do |func, value|
    if String(func[:concat]).empty? then
      func[:concat] = String(value)
    else
      func[:concat] = String(func[:concat]) + " " + String(value)
    end
  end

  finalize do |func|
    func.result = func[:concat]
  end
end

db.execute "select grouper, group_concat(name) from tests group by grouper" do |row|
  p row
end

db.close

Test with a SQLite3 database containing the following:

CREATE TABLE tests (id integer primary key, name test, grouper text);
INSERT INTO "tests" VALUES(1, 'Foo', '1');
INSERT INTO "tests" VALUES(2, 'Bar', '1');
INSERT INTO "tests" VALUES(3, 'ABC', '2');
INSERT INTO "tests" VALUES(4, 'DEF', '2');
INSERT INTO "tests" VALUES(5, 'GHI', '2');
INSERT INTO "tests" VALUES(6, 123, '3');

Gives these results:

["1", "Foo Bar"]
["2", "ABC DEF GHI"]
["3", "123"]