How to simulate group_concat in plain sql

Question

I am using the hxtt sql driver for csv files. It only supports plain sql. is there a way to simulate group concat using plain sql statements?

Answer 1

How plain? If you can use triggers then you can do it fairly simply. I've used this trick before in SQLite3 when I've needed a group_concat() that allows me to specify the order in which values are to be concatenated (SQLite3 does not provide a way to do that).

So let's say we have a table like this:

CREATE TABLE t(v TEXT NOT NULL, num INTEGER NOT NULL UNIQUE);

and you want to concatenate the values of v ordered by num, with some separator character, let's say a comma.

CREATE TEMP TABLE c(v TEXT);
CREATE TEMP TABLE j(v TEXT);

CREATE TEMP TRIGGER j_ins BEFORE INSERT ON j
FOR EACH ROW
BEGIN
    UPDATE c SET v = v || ',' || NEW.v;
    INSERT INTO c (v) SELECT NEW.v WHERE NOT EXISTS (SELECT * FROM c);
    SELECT RAISE(IGNORE);
END;

Now we can:

INSERT INTO j (c) SELECT v FROM t ORDER BY num;
SELECT v FROM c; -- this should output the concatenation of the values of v in t
DELETE FROM c;

Finally, this is a sqlite3 session showing that this works:

SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t(v TEXT NOT NULL, num INTEGER NOT NULL UNIQUE);
sqlite> CREATE TEMP TABLE c(v TEXT);
sqlite> CREATE TEMP TABLE j(v TEXT);
sqlite> CREATE TEMP TRIGGER j_ins BEFORE INSERT ON j
   ...> FOR EACH ROW
   ...> BEGIN
   ...>     UPDATE c SET v = v || ',' || NEW.v;
   ...>     INSERT INTO c (v) SELECT NEW.v WHERE NOT EXISTS (SELECT * FROM c);
   ...>     SELECT RAISE(IGNORE);
   ...> END;
sqlite> insert into t (v, num) values (1, 0);
sqlite> insert into t (v, num) values (31, 1);
sqlite> insert into t (v, num) values (52, 2);
sqlite> insert into t (v, num) values (0, 3);
sqlite> SELECT v FROM c;
sqlite> INSERT INTO j (v) SELECT v FROM t ORDER BY num;
sqlite> SELECT v FROM c;
1,31,52,0
sqlite> SELECT v FROM j;
sqlite> DELETE FROM c;
sqlite> 

Now, this isn't pure SQL, because it depends on triggers. Between recursive triggers and all the ways to do conditionals in SQL you have a Turing complete system. But if you don't have triggers, or any procedural extensions, no generator tables... then not so much.

I know nothing about hxtt, so maybe this won't help you. But SQLite3 can deal with CSV, so maybe SQLite3 can help you...