Search code examples
sqlsqlite

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?

Solution

  • 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';