CODEWITHSUNDEEP
sql-server-2005column-alias
ram singh
ram singh

Reputation: 1

Alias creation in SQL Server

How we can create alias of a column in table in SQL Server?

Upvotes: 0

Views: 222

Answers (3)

Damian Leszczyński - Vash
Damian Leszczyński - Vash

Reputation: 30875

Creation of aliases is very easy 

SELECT tableColumnName as ColumnAlias FROM Table

Another thing is usage of the aliases, you must remember that the aliases are available after projection (select) this mean that you can't use those aliases in FROM, WHERE, GROUP BY, HAVING sections. Is allowed only in ORDER BY.

EDIT: Usage of aliases

Tables:

STACK 
 - STACK_ID
 - STACK_NAME
 - STACK_ORDER
 - STACK_MIN
 - STACK_MAX

Wrong statement:

    SELECT 
     STACK_NAME, 
     STACK_MIN, 
     STACK_MAX, 
     STACK_MIN + STACK_MAX as STACK_SUM 
   FROM 
    STACK WHERE STACK_SUM = 10;

We use in WHERE section column that is not available on this level.

To solve this we have two options

Option One - We do the calculation in where statement

    SELECT 
     STACK_NAME, 
     STACK_MIN, 
     STACK_MAX, 
     STACK_MIN + STACK_MAX as STACK_SUM 
   FROM 
    STACK WHERE STACK_MIN + STACK_MAX = 10;

Option Two - We create a temporary table 

WITH STACK_SUM_TAB AS (
 SELECT 
  STACK_NAME, 
  STACK_MIN, 
  STACK_MAX, 
  STACK_MIN + STACK_MAX as STACK_SUM 
 FROM STACK 
)

 SELECT 
  STACK_NAME, 
  STACK_MIN, 
  STACK_MAX, 
  STACK_SUM 
 FROM STACK_SUM_TAB WHERE STACK_SUM = 10;

Upvotes: 1

ashish.chotalia
ashish.chotalia

Reputation: 3746

SELECT columnname AS [ColumnAliasName] FROM [TableName]

Upvotes: 0

Darin Dimitrov
Darin Dimitrov

Reputation: 1039418

select somecolumn as foo from bar where foo = 5

Upvotes: 0

Related Questions