Mukul Jain
Mukul Jain

Reputation: 1927

SQLite3 joining rows to form a single row

I am building an android application which queries a database for a bus number, and bus departure time.

The tables are thus -

Table 1: Route - _id, routenumber

Table 2: Routetimedetails - _id, routetime, routeid

My current query is this

select routenumber, route._id, routetime from route, routetimedata where route._id = 2 and routeid = route._id

and the result shows up like this >

routenumber | route._id | routetime
-------------------------------------
BIAS10      | 2         | 0945

BIAS10      | 2         | 1810

BIAS10      | 2         | 1945

BIAS10      | 2         | 0710

is there a way in sqlite3 to show the result in a single column like this -

BIAS10      | 2         | 0710, 0945, 1810, 1945

Thanks Mukul

Upvotes: 0

Views: 74

Answers (1)

Mukul Jain
Mukul Jain

Reputation: 1927

I find that the Group_Concat() function does this easily

select routenumber, route._id, Group_concat(routetime) from route, routetimedata where route._id = 2 and routeid = route._id

Upvotes: 1

Related Questions