How do I name the output column in a sqlite values clause?

The VALUES form of a query does not have any mechanism for you to specify the column name. (The VALUES clause is intended to be used in CTEs or views where you can specify the column names elsewhere.)

As it happens, the columns returned by VALUES do have names (but they are undocumented):

sqlite> .header on
sqlite> .mode columns
sqlite> values (42);
column1
----------
42

In any case, even if that name does not survive the subquery, an empty column name is no problem at all:

select "" from (values (1),(2),(3),(4),(5));

To apply column name(s), wrap a CTE around it:

WITH T(my_column) AS (
  VALUES (1),(2),(3),(4),(5)
)
SELECT * FROM T;

or use a compound query (the WHERE 0 suppresses the row from the first query):

SELECT NULL AS my_column WHERE 0
UNION ALL
VALUES (1),(2),(3),(4),(5);

with cte(my_column_alias) as 
  (values (1),(2),(3),(4),(5))
select * from cte;

select 1 a union all 
select T.* from (values (1),(2),(3),(4),(5)) as T;

a
---
1
1
2
3
4
5

little trick and now you has column a


Although I recommend @Catherine's provided CTE answer - As it not only seems to perform faster in some instance then the answer I'll provide, but encourages development patterns and approaches that relate to principals like re-usability and single responsibility.

Though this answer (and probably all these answer) depend on PRAGMA settings (which I don't have enough knowledge on at this time to cover), It seems it is possible to both reference to generated column names and alias them.

That is, columns of the VALUES table are generated with column headers: column1, column2 and so on; So, you just alias a column by explicitly referencing the specific, generated column name.

SELECT
   v.[column1] [Id]
  ,v.[column2] [Name]
FROM (VALUES (1, 'Alex'), (2, 'Brad'), (3, 'Mary'), (4, 'Jennifer')) [v]

Screenshot of Executing SQL in DB Browser Sqlite

Tags:

Sqlite