Renaming columns in query results

By default, the heading for each column of a result set is the name of the expression supplied in the select list. For expressions that are column values, the heading will be the column name. In embedded SQL, one can use the DESCRIBE statement to determine the name of each expression returned by a cursor. Other application interfaces also support querying the names of each result set column through interface-specific mechanisms. The sa_describe_query system procedure offers an interface-independent means to determine the names of the result set columns for an arbitrary SQL query.

You can override the name of any expression in a query's SELECT list by using an alias, as follows:

SELECT column-name [ AS ] alias

Providing an alias can produce more readable results. For example, you can change DepartmentName to Department in a listing of departments as follows:

SELECT DepartmentName AS Department,
   DepartmentID AS "Identifying Number"
FROM Departments;

Department	Identifying Number
R & D	100
Sales	200
Finance	300
Marketing	400
...	...

Usage

Using spaces and keywords in an alias In the example above, the "Identifying Number" alias for DepartmentID is enclosed in double quotes because it contains a blank. You also use double quotes if you want to use keywords or special characters in aliases. For example, the following query is invalid without the quotation marks:

SELECT DepartmentName AS Department, DepartmentID AS "integer" FROM Departments;

Name space occlusion Aliases can be used anywhere in the SELECT block in which they are defined, including other SELECT list expressions that in turn define additional aliases. Cyclic alias references are not permitted. If the alias specified for an expression is identical to the name of a column or variable in the name space of the SELECT block, the alias definition occludes the column or variable. For example:

SELECT DepartmentID AS DepartmentName FROM Departments WHERE DepartmentName = 'Marketing'

will return an error, "cannot convert 'Marketing' to a numeric". This is because the equality predicate in the query's WHERE clause is attempting to compare the string literal "Marketing" to the integer column DepartmentID, and the data types are incompatible.

Note

When referencing column names you can explicitly qualify the column name by its table name, for example Departments.DepartmentID, to disambiguate a naming conflict with an alias.

Transact-SQL compatibility Adaptive Server Enterprise supports both the SQL/2008 AS keyword, and the use of an equals sign, to identify an alias for a SELECT list item.
See also Writing compatible queries.

Renaming columns in query results

Usage

Note

See also