CODEWITHSUNDEEP
sqlite
feihtthief
feihtthief

Reputation: 7063

How do I name columns in a VALUES clause?

For this query in SQLite 3.17.0 :

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

there is no name for first column of T:
1
2
3
4
5

How do I name/alias the first column of T, or refer to it by index?

Upvotes: 26

Views: 10840

Answers (6)

Mark A. Donohoe
Mark A. Donohoe

Reputation: 30388

Here's a collection of the various values-based syntaxes I use. The first two are especially handy for unit tests where you can also use a memory-based database instance.

SELECT FROM VALUES

This simply returns the values as a result set with the named columns.

SELECT
    column1 as Id,
    column2 as Name
FROM (VALUES
    (1, 'Alex'),
    (2, 'Brad'),
    (3, 'Mary'),
    (4, 'Jennifer')
)

COMMON TABLE EXPRESSIONS (CTE)

Similar to the above, there's the CTE approach. This creates a 'name' for the value-selected data so you can use it elsewhere in your query as if it were a table. This allows you to create 'mini' tables which you can then join into the final output. (Here however, I'm just creating a single one called 'myTable'.)

WITH myTable(id, name) AS (
VALUES
    (1, 'Alex'),
    (2, 'Brad'),
    (3, 'Mary'),
    (4, 'Jennifer')
)
SELECT id, name
FROM myTable;

TEMPORARY TABLES (Including for completeness)

This is similar to the CTE above but the named results exist for the lifetime of the connection (or until you manually drop them, if sooner.) That's why unlike the above two which can be run without issue, this will fail if the table already exists, so make sure to 'drop' it first, or clear-then-repopulate it with the new data.

CREATE TEMP TABLE myTable (id Integer, name TEXT);

INSERT INTO myTable
VALUES 
    (1, 'Alex'),
    (2, 'Brad'),
    (3, 'Mary'),
    (4, 'Jennifer');

SELECT * FROM myTable;

Upvotes: 0

Brady Holt
Brady Holt

Reputation: 2924

Per 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".

So, you could change to:

SELECT T.a AS my_column_alias 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 "my_column_alias" is your column name/alias.

my_column_alias
---------------
1
2
3
4
5

Upvotes: 1

Catherine Devlin
Catherine Devlin

Reputation: 7743

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

Upvotes: 30

Brett Caswell
Brett Caswell

Reputation: 1504

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

Upvotes: 5

CL.
CL.

Reputation: 180080

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

Upvotes: 16

dfy167
dfy167

Reputation: 41

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

Upvotes: 4

Related Questions