sql - Android Sqlite tow Rows as tow Columns, Pivots? -

came know called pivots, couldn't examples sqlite without aggregation.

schema:

readonly table foos(_id, foo) (10 max records) readonly table bars(_id, bar) (300 max records) readonly table items(_id, foo_id, bar_id, item1, item2) (3000 max records) 

output required:

select  foos._id,   foos.foo,   bars.bar,   items.item1(bar_id=1),   items.item1(bar_id=2),   items.item2(bar_id=1)  

i need 2 columns.

am missing obvious. performance hit. table design wrong, correct me!

update:

select     foo_id,     group_concat(case bar_id when 1 title else null end) 'item1',     group_concat(case bar_id when 2 title else null end) 'item2'      items      foo_id=2 group     foo_id 

this ended with.

you should check out common mysql queries, there lot of samples, including pivots, may you.

you can find looking in this section.