SQL server column alias from another query

Question

Why does this code not work in SQL Server 2005?

select c.price AS (select CityName from Cities ci where ci.cityid= c.cityid)
from prices c
where cityid=1

Thanks.

Answer 1

What a question! Because where you're using a sub-query is the alias place. Alias is what you give to long names to use them easily in the other parts of your queries. I think what's on your mind is something like this code:

Select  c.price
,   ci.CityName
    From    prices  As  c
    Inner   Join
        Cities  As  ci 
    On  ci.cityid   =   c.cityid

And also please write your code in nested-style. It'd be easy to read and ofcourse more easy to understand for yourself.

UPDATE:

If you need the city list as column names and prices below them, you should use "PIVOT" operator. Like this code:

Select  PivotedResult.[1]   As  US
,   PivotedResult.[2]   As  UK
,   PivotedResult.[3]   As  IR
    From    prices  As  c
    Pivot   (
        Sum(c.price)
        For c.cityid    In  (
            [1]
        ,   [2]
        ,   [3]
        )
    )   As  PivotedResult

Cheers

Answer 2

EDITED

select c.price AS [Tokyo] from prices c where c.cityid=1

No, that is not possible and not likely to receive any love from the ISO standards group for SQL. You're mixing the purpose of RDBMS and front-end semantics.

Imagine what would happen if you had 2 rows of data, one for [Tokyo] and one for [Kyoto]. what would you name the column? The better alternative is to produce 2 columns as shown below and let the front-end use the label from the 2nd column.

---

Perhaps

select c.price
      ,(select CityName from Cities ci where ci.cityid= c.cityid) AS CityName
  from prices c
 where cityid=1

The term "column alias" is used when you wish to name the output column to the caller of the query. In this case, the CityName from the subquery is returned and the 2nd column in that query is named as "CityName".

Assuming cityid is unique, you won't have any problems with the subquery returning multiple results - that is not allowed in a SELECT expression.

Unless it is possible for prices.cityid not to exist in the Cities table (in which case your query is good), it would be better rewritten

select c.price, ci.CityName
  from prices c
  join Cities ci on ci.cityid= c.cityid
 where cityid=1

Answer 3

Because nowhere in any specification or standard of SQL does it say it should work. SQL, from any rdbms, is about the data, not how it's presented.