How to get a fixed amount of results per row?

Question

How can I build a query in this format using sqlite3 database?

CREATE TABLE sample (integer foo); INSERT INTO sample VALUES (1); ... INSERT INTO sample VALUES (10); 

Result Format

 1,2,3 4,5,6 7,8,9 10 

Answer 1

You will need to add some criteria to group them:

 CREATE TABLE sample (integer foo, char(1) bar); INSERT INTO sample VALUES (1, 'a'), (2, 'a'), (3, 'a'), (4, 'b'), (5, 'b'), (6, 'b'), (7, 'c') ...; SELECT GROUP_CONCAT(foo ORDER BY foo, ',') FROM sample GROUP BY bar 

Edit:

Try the following:

 select group_concat(foo) from ( select s1.foo, (count(*) - 1) / 3 grp from sample s1 join sample s2 on s1.rowid >= s2.rowid group by s1.rowid ) final group by grp 

Answer 2

This might not be the best solution (and has some edge cases - if you have a value of 3 that is not null) and probably can be placed in a single request, but I need RowId to join. However, he should do the trick:

 CREATE TEMP TABLE split1 (foo1 int); CREATE TEMP TABLE split2 (foo2 int); CREATE TEMP TABLE split3 (foo3 int); INSERT INTO split1 SELECT foo FROM sample WHERE foo % 3 = 1 ORDER BY foo INSERT INTO split2 SELECT foo FROM sample WHERE foo % 3 = 2 ORDER BY foo INSERT INTO split3 SELECT foo FROM sample WHERE foo % 3 = 0 ORDER BY foo SELECT CASE WHEN foo2 IS NULL THEN foo1 WHEN foo3 IS NULL THEN foo1||','||foo2 ELSE foo1||','||foo2||','||foo3 END FROM split1 LEFT JOIN split2 ON split1.RowId = split2.RowId LEFT JOIN split3 ON split2.RowId = split3.RowId