Clever Geek Handbook

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?

+3


source to share


4 answers 


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


+1


source


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));
+2


source 


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

+1


source


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.

0


source






More articles:


All Articles