Assign Names to Columns Without Known Name

Normally, we define column names using the create statement and alias them in select if needed. However, to alias a column using select you must first know its the original name. This is not the case when using table functions, unnest or values. The only way to assign names to such columns is on the basis of their position.

Option 1: Using Aliases in the from Clause

Besides table aliases, intermediate level SQL-92 also supports renaming columns in the from clause:

SELECT b, a
  FROM (VALUES (1, 'a')
       ) t(a, b)

The column names produced by values are implementation-depended.0 That is not useful for portable SQL. To circumvent that glitch, the example assigns the table alias t1 followed by a list of column aliases in parenthesis (just c1 in that case). The select clause can now refer to c1 in a portable manner.

Option 2: Using Common-Table-Expressions (with)

Starting with SQL:1999 the with clause can also be used to rename columns based on their position—i.e., without knowing their original name:

WITH t (a, b) AS (
  VALUES (1, 'a')
)
SELECT b, a
  FROM t

Compatibility

Even though from aliases were already required for intermediate SQL-92 and became mandatory in SQL:1999, with is nevertheless better supported:

1. Only for values and select: FROM (VALUES | SELECT …) t(c1, c2)
2. Only for select: FROM (SELECT …) t(c1, c2)
3. Only for values, but then required: FROM (VALUES …) t(c1, c2)
4. Only allowed at the very begin of a statement. E.g. with...insert...select.