Turn SQLite columns to rows

Question

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?

Lardon

if you want it temporary then you can easily store the result in a List or a Map if you need both name and value. – Wardwarde

the ID can't repeat. It should be unique. So you can't have same IDs for multiple rows. – Cystolith

Thanks for the reply Darshan, I'm already storing the result in a MatrixCursor, but it causes alot of complexity when using a CursorAdaptor. The simplest way would be to massage the data into the preferred layout through the selection. My MatrixCursor is already working, I'm just looking for a more elegant solution. @Vlad that's a good point, I'm not terribly interested in retaining the ID since I already know the value (I'm querying with it). But the main point is to have the columns of each of the results added each as a new row. – Lardon

Answer 1

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.

Pertinacity

Answer 2

Why not just JOIN on a values table and then CASE the correct column?

SELECT original_table._id,
  CASE t.column1 WHEN 1 THEN 'value1' WHEN 2 THEN 'value2' WHEN 3 THEN 'value3' ELSE 'value4' END AS name, 
  CASE t.column1 WHEN 1 THEN  original_table.value1 WHEN 2 THEN  original_table.value2 WHEN 3 THEN  original_table.value3 ELSE  original_table.value4 END AS value
FROM (
  select 1 _id, 10 value1 , 11 value2 , "string value" value3 , 13 value4
  union all 
  select 2, 14,  "string value",null,17
) original_table
JOIN (VALUES(1),(2),(3),(4)) t
--ORDER BY _id, t.column1;

The result:

id  name    value
1   value1  10
1   value2  11
1   value3  string value
1   value4  13
2   value1  14
2   value2  string value
2   value3  {null}
2   value4  17

Alternatively add this to avoid nulls:

 ON (t.column1=1 AND original_table.value1 IS NOT NULL) OR (t.column1=2 AND original_table.value2 IS NOT NULL) OR (t.column1=3 AND original_table.value3  IS NOT NULL) OR (t.column1=4 AND original_table.value4  IS NOT NULL) 

Yard