CODEWITHSUNDEEP
sqlsqlite
Ziminji
Ziminji

Reputation: 1306

How can I generate a list of values as rows from a query?

I want to generate a list of names from an SQLite SELECT query as rows. This states:

According to the SQL92 standard, a VALUES expression should be able to stand on itself. For example, the following should return a one-column table with three rows:

VALUES 'john', 'mary', 'paul';

In theory, the result set is:

 
john
mary
paul

But this is unsupported in SQLite. I can UNION a series of SELECT statements:

SELECT 'john' AS name
UNION ALL SELECT 'mary' AS name
UNION ALL SELECT 'paul' AS name;

Is there an easier way?

Upvotes: 2

Views: 2077

Answers (3)

Vainstein K
Vainstein K

Reputation: 202

This did not work in SQLite 3.7, but works in SQLite 3.26:

SELECT * FROM (VALUES ('john'), ('mary'), ('paul')) ;

Unlike say PostgreSQL, however, SQLite won't let us alias the columns of such a rowset: SQLite itself names the columns as column, column2, and so on. To assign our own aliases, we must alias SQLite's aliases:

SELECT column1 AS name, column2 AS age
FROM (VALUES ('john',33), ('mary',44), ('paul',55)) ;

gives

name        age       
----------  ----------
john        33        
mary        44        
paul        55

Upvotes: 2

janscas
janscas

Reputation: 629

I found more expressive to use:

SELECT 'john' AS name UNION ALL VALUES ('mary'), ('paul')

You need to specify the first select to gain column aliases.

This construct is easier to read and has fewer limitations as stated in the sqlite documentation:

The phrase "VALUES(expr-list)" means the same thing as "SELECT expr-list". The phrase "VALUES(expr-list-1),...,(expr-list-N)" means the same thing as "SELECT expr-list-1 UNION ALL ... UNION ALL SELECT expr-list-N". Both forms are the same, except that the number of SELECT statements in a compound is limited by SQLITE_LIMIT_COMPOUND_SELECT whereas the number of rows in a VALUES clause has no arbitrary limit.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659367

For all I know, what you have there already is the best compound SELECT statement SQLite has to offer. One tiny thing: you need the column alias once only.

SELECT 'john' AS name
UNION ALL SELECT 'mary' 
UNION ALL SELECT 'paul';

Upvotes: 0

Related Questions