Android SQLite: combine multiple rows into one

Question

I have two SQLite tables:
- users:

id    username    age
1     jack        24
2     lisa        19

- images:

id    username    image
1     jack        jackImg1.jpg
2     jack        jackImg2.jpg
3     lisa        lisaImg1.jpg

I use LEFT JOIN to get all jack's images:

SELECT users.*, images.image FROM users
LEFT JOIN images ON images.username = users.username
WHERE users.username = 'jack';

the result is like this:

id    username    age    image
1     jack        24     jackImg1.jpg
1     jack        24     jackImg2.jpg

But I'd like to get a result like this:

id    username    age    image1          image2
1     jack        24     jackImg1.jpg    jackImg2.jpg

How can I achieve a result like above table?
Note: the number of images can vary from zero to any number(like 15, 20,...)

That's not how SQL works. See how you would have to generate new column names image1 and image2? What if you query several users with a different number of attached images? — Henry
– Henry, Commented Sep 1, 2019 at 6:08

forpas · Accepted Answer · 2019-09-01 08:16:10Z

0

SQLite does not provide such functionality to pivot or unpivot tables.
What you can do is group by user and use the group_concat() aggregate function to achieve a similar result:

SELECT 
  u.id, u.username, u.age, 
  group_concat(i.image, ' ') images 
FROM users u LEFT JOIN images i
ON i.username = u.username
WHERE u.username = 'jack'
GROUP BY u.id, u.username, u.age

I used space as a separator between the images, you can use any char you want.
See the demo.
Results:

| id  | username | age | images                    |
| --- | -------- | --- | ------------------------- |
| 1   | jack     | 24  | jackImg1.jpg jackImg2.jpg |

answered Sep 1, 2019 at 8:16

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Android SQLite: combine multiple rows into one

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related