Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Turn SQLite columns to rows

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?

like image 716
Adrian Stratienco Avatar asked Mar 14 '17 07:03

Adrian Stratienco


People also ask

How to delete a row in SQLite?

The SQLite DELETE statement allows you to delete one row, multiple rows, and all rows in a table. The syntax of the SQLite DELETE statement is as follows: First, specify the name of the table which you want to remove rows after the DELETE FROM keywords.

How do you name a column in SQLite?

The column is named in a table or column CHECK constraint not associated with the column being dropped. The column is used in a foreign key constraint . The column is used in the expression of a generated column . The column appears in a trigger or view. 5.1. How It Works SQLite stores the schema as plain text in the sqlite_schema table .

How do I change the format of a table in SQLite?

Making Other Kinds Of Table Schema Changes The only schema altering commands directly supported by SQLite are the " rename table ", " rename column ", " add column ", " drop column " commands shown above. However, applications can make other arbitrary changes to the format of a table using a simple sequence of operations.

What is the ALTER TABLE command in SQLite?

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table, to rename a column within a table, or to add a new column to an existing table.


1 Answers

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.

like image 127
CL. Avatar answered Sep 18 '22 20:09

CL.