I'd like to return multiple rows as a single row to be handled by an Android Cursor Adapter.
I currently have a table with a date column and I'd like to return, as a single row, all the rows that have the same date.
Consider the following table:
ID | Name | Date
-------------------------------
1 | 'Mark' | '08/06/15'
2 | 'Peter' | '08/06/15'
3 | 'Henry' | '08/06/15'
4 | 'Bob' | '17/04/16'
5 | 'Tony' | '23/08/13'
6 | 'Tim' | '17/04/16'
I'd like to query the results as follows:
Date | Names
------------------------------------
'08/06/15' | 'Mark, Peter, Henry'
'17/04/16' | 'Bob, Tim'
'23/08/13' | 'Tony'
Using this link I was able to obtain the following query:
SELECT t1.id, GROUP_CONCAT(t1.Name ) AS Names
FROM Table1 t1 JOIN Table2 t2 ON t1.ID = t2.ID
GROUP BY t1.ID;
However, since all the data is from the same table, and I know the dates to query in advance, I was hoping to use JOIN with an inputted array and have SQLite parse it (ie. like unnest in Postgres) as if it came from an actual table.
The array would be something like:
['08/06/15', '17/04/16', '23/08/13', '09/08/18']
This can probably also be done by nesting SQL queries, but I'd like an optimized solution if possible.
SQLite has no array type.
The simplest way would be to use the IN operator:
SELECT ID, group_concat(Name) AS Names
FROM MyTable
WHERE Date IN ('08/06/15', '17/04/16', '23/08/13', '09/08/18')
GROUP BY ID;
If you really want to use a join, you can construct a virtual table with a VALUES clause (in Android Jelly Bean or later), or a compound query:
SELECT ID, group_concat(Name) AS Names
FROM MyTable
JOIN (VALUES('08/06/15'), ('17/04/16'), ('23/08/13'), ('09/08/18'))
ON Date = column1
GROUP BY ID;
SELECT ID, group_concat(Name) AS Names
FROM MyTable
JOIN (SELECT '08/06/15' AS Date UNION ALL
SELECT '17/04/16' UNION ALL
SELECT '23/08/13' UNION ALL
SELECT '09/08/18' ) USING (Date)
GROUP BY ID;