sqlite3 - Merge multiple rows as one row where id is same

Question

I has a problem:

In my SQLite (sqlite3 on android) database I have a table like so:

id  | text
---------------------
id1:
abcdhgjj

id1:
kjhjmnl

id1:
mnbvjgfh

id2:
lhghdb

id2:
ghduhjgf

and I'd like to get to:

id  | text
---------------------
id1:
abcdhgjj
kjhjmnl
mnbvjgfh

id2:
lhghdb
ghduhjgf

How can i do it Using SQLite Query??

Does anyone have an idea how to make this work?

Thanks!

Kurt

i shoul say that, i'd like to merge text fields with ENTER character (multi row text). – Kurt

Answer 1

SQLite supports group_concat(), so you can try:

select id, group_concat(text, ' ') as text
from table t
group by id;

Note that you have no control over the order of the concatenation.

EDIT:

You should be able to enter a newline directly into the query:

 SELECT id, group_concat(text, '') AS text
 FROM table t
 group by id;

Undertenant

thanks gordon. can i put enter character instead of ' ' ?. and if you say me yes, what is the enter character? – Kurt

This isn't working. Kindly check and reply – Phenobarbitone

@NehemiahNarzary . . . This is a rather old question. If you have a question about your data, I would suggest that you ask a new question. – Undertenant

Answer 2

You can try this

SELECT id,
group_concat(text, '<br>') as text
FROM tablename 
GROUP BY I'd;

This worked for me.

Phenobarbitone