Get all row ids in one string in SQLite

Question

I have a table in SQLite which contains some data.
Like this

what i want is a String which contains all the ids separated by a semicolon (;) character, like

 1;2;3;4;5;6;7

I know there is group concat.
but I cannot figure out how to do that with this sample.

And there is another method called COALESCE I found here, but in SQLite we cannot declare variables.

Is there any way to do this in SQLite?

i know we can get cursor and get all ids programmatically but i want to is there a way for do this in query? — max, Dec 23 '15 at 11:31
`SELECT GROUP_CONCAT(id, ';') AS result FROM tab` Is that what you need? — Lukasz Szozda, Dec 23 '15 at 11:32
sorry some one else add comment and deleted immediately anyway in group concat must we have 'group by' at the end right? — max, Dec 23 '15 at 11:37
@max We can but if not provided it will get all records in one group, if you need filtering add sth like `WHERE id < 8` — Lukasz Szozda, Dec 23 '15 at 11:38

score 0 · Accepted Answer · answered Dec 23 '15 at 11:42

You can use aggregation function group_concat(X,Y) to achieve it:

The group_concat() function returns a string which is the concatenation of all non-NULL values of X. If parameter Y is present then it is used as the separator between instances of X. A comma (",") is used as the separator if Y is omitted. The order of the concatenated elements is arbitrary.

SELECT GROUP_CONCAT(id, ';') AS result 
FROM tab_name
-- WHERE id < 10; -- if needed

If you don't specify GROUP BY ... clause all records will be treated as one group.

Get all row ids in one string in SQLite

1 Answers1