How can I name the output column in the sqlite values expression?
If I run this query in sqlite3 (3.17.0)
select T.* from (values (1),(2),(3),(4),(5)) as T;
I am getting the below output where there is no name for the first column T.
----------
1
2
3
4
5
How can I give the first column T a name / alias, or alternatively is there a way to reference it by index somehow?
with cte(my_column_alias) as
(select * from (values (1),(2),(3),(4),(5)))
select * from cte;
The VALUES query form has no mechanism for specifying a column name. (The VALUES clause is intended for use in CTEs or views, where you can specify column names elsewhere.)
As it happens, the columns returned by VALUES have names (but they are undocumented):
sqlite> .header on sqlite> .mode columns sqlite> values (42); column1 ---------- 42
In any case, even if this name is not stored in the subquery, an empty column name is not a problem:
select "" from (values (1),(2),(3),(4),(5));
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 have a column
In the SQLite Documentation ,
The phrase "VALUES (expr-list)" means the same as "SELECT expr-list". The phrase "VALUES (expr-list-1), ..., (expr-list-N)" means the same as "SELECT expr-list-1 UNION ALL ... UNION ALL SELECT expression-list-N" ...
So you can change to:
SELECT T.a FROM (
SELECT 1 as a
UNION ALL
SELECT 2 as a
UNION ALL
SELECT 3 as a
UNION ALL
SELECT 4 as a
UNION ALL
SELECT 5 as a
) as T;
Then "a" is your column name / alias.