How to get a fixed amount of results per row?

Question

How can I build a query with this format with a sqlite3 database?

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

Format of the result

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

Answer 1

You'll have 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 this:

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 3 value that is not null), and can probably be put into one query, but I needed the RowId to join on. It should do the trick, though:

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