How to simulate group_concat in plain sql

Question

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

Answer 1

How clear? If you can use triggers, you can do it quite simply. I used this trick before in SQLite3 when I need group_concat (), which allows me to specify the order in which the values ​​should be combined (SQLite3 does not provide a way to do this).

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 combine the v values ​​ordered with num with some separator character, 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 is working:

 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 is not pure SQL, because it depends on triggers. Between recursive triggers and all methods of executing conditional expressions in SQL, you have a complete Turing system. But if you do not have triggers or any procedural extensions, there are no generator tables ... then not so many.

I don't know anything about hxtt, so maybe this will not help. But SQLite3 can work with CSV, so maybe SQLite3 can help you ...