I am trying to convert a query which returns a single row with multiple columns to multiple rows with one or multiple columns. As an example my query looks like the following visualized:
SELECT _id, value1, value2, value3, value4 FROM table WHERE _id IS 1;
RESULT:
_id value1 value2 value3 value4
----------------------------------------
1 10 11 12 13
I would like to have my results look like the following:
name value
--------------
value1 10
value2 11
value3 12
value4 13
but I can also work with something like this:
value
-----
10
11
12
13
Currently I'm manually converting this using a MatrixCursor in Android, but the ideal situation would be to have the cursor already in a usable state. Is there a way to either modify my query to retrieve the results in my desired schema or to use something like a temp table to achieve the desired end state?
Also, is there a common term to describe this functionality within a Database?
There is no built-in mechanism that could do this automatically.
If you know the columns, you can construct a query that handles each column individually:
SELECT _id, 'value1' AS name, value1 AS value FROM tab WHERE _id = 1
UNION ALL
SELECT _id, 'value2' AS name, value2 AS value FROM tab WHERE _id = 1
UNION ALL
SELECT _id, 'value3' AS name, value3 AS value FROM tab WHERE _id = 1
UNION ALL
SELECT _id, 'value4' AS name, value4 AS value FROM tab WHERE _id = 1;
This is not necessarily any better than a MatrixCursor.