Select all values in a column into a string in SQLite?

Question

In SQL Server, I can do this with the help of Cursor (to loop through each row in the result set and build my string). But I don't know how to do the same in SQLite, this task is not like the so-called Pivoting which as far as I know, we have to know the exact rows we want to turn to columns. My case is different, I want to select all the values of a specified column into a string (and then can select as a column of 1 row), this column can have various values depend on the SELECT query. Here is a sample of how it looks:

A    |    B    
------------
1    |    0
8    |    1
3    |    2
...     ....

I want to select all the values of the column A into a string like this "183..." (or "1,8,3,..." would be fine).

This can be used as a column in another SELECT, I have to implement this because I need to display all the sub-infos (on each row of column A) as a comma-separated list in another row.

I don't have any idea on this, it seems to need some procedural statements (such as placed in a procedure) but SQLite limits much on how I can do with loop, variable declaration,...I'm really stuck. This kind of task is very common when programming database and there is no reason for me to refuse doing it.

Please help, your help would be highly appreciated! Thanks!

What are your desired results? Just 1,8,3? – sgeddes Apr 21 '13 at 14:31 — sgeddes, Apr 21 '13 at 14:31

score 11 · Accepted Answer · answered Apr 21 '13 at 14:32

11

If you're just trying to get all the values from Column A into a single record, then use GROUP_CONCAT:

select group_concat(a, ',')
from yourtable

SQL Fiddle Demo

answered Apr 21 '13 at 14:32

sgeddes

62,311
6
61
83

this is exactly I want, thank you very much. I didn't thought it's even simpler than in SQL Server. :) – King King Apr 21 '13 at 14:56
is there some way to use group_concat() with order by? I mean I want to change the order of the values in the comma-separated list. Thank you! – King King Apr 21 '13 at 15:08
@KingKing -- see this post -- you'll need to use a subquery: http://stackoverflow.com/questions/1897352/sqlite-group-concat-ordering - Best regards – sgeddes Apr 21 '13 at 16:44

Robert Co · Answer 2 · 2013-04-21T15:11:25.747

0

The main problem is that you are think like an application programmer. SQL does a lot of things for you.

SELECT * 
  FROM tableA
 WHERE A IN (SELECT A 
                  FROM tableB)

No need to resort to cursors, stored procedures and multiple queries.

edited Apr 21 '13 at 15:11

answered Apr 21 '13 at 14:35

Robert Co

1,715
8
14

I don't understand your query well, what is the A.id? A is a column not a table. Thanks. – King King Apr 21 '13 at 14:58
Can you please explain your answer? – Hussien Fahmy Jan 29 '22 at 14:35

Select all values in a column into a string in SQLite?

2 Answers2