Skip to content

How do I use a function result as my alias?

I am wondering if it is possible to use an SQL function to name the column of a query. As a simple example:

SELECT id AS SUBSTR('xAlias', 2) FROM foo

should theoretically return

Alias
------
...
results
...

If we can, which databases would this be available for? If not, then why?

Answer

AFAIK, there is no SQL way in any major DBMS product to support this.

To name PIVOT columns in Oracle, for name in.. as seems like the best option.
Apart from that, it’s over to dynamic SQL.


Original answer below

To name the column of a query, the ANSI standard is simply to add the new name after the column (derived or base) prepended by “AS”:

SELECT id, field1, field2 * qty FROM foo

All columns renamed below:

SELECT id AS RenamedID, field1 AS Col1, field2 * qty AS ExtendedTotal FROM foo

This standard works for pretty much all major database systems.
There are some vendor specific variations, such as SQL Server allowing the equal sign

SELECT RenamedID=id FROM foo

And most DBMS allow the omission of the “AS” keyword

SELECT id RenamedID FROM foo