How do I get this to work, it works without the Where Clause, otherwise with the Where clause, i get the obvious error, but that's basically what needs to be done, anyone know how to approach this?
select ID,
Name,
case T.N
when 1 then City1
when 2 then City2
when 3 then City3
end as City,
case T.N
when 1 then State1
when 2 then State2
when 3 then State3
end as State
from YourTable
cross join (values(1),(2),(3)) as T(N)
Where City is NOT Null
You can't use the alias in the WHERE clause. Either repeat the expression (messy) or else put your SELECT in a subquery and then put the WHERE clause in the outer query:
SELECT Id, Name, City, State
FROM
(
SELECT
ID,
Name,
CASE T.N
WHEN 1 THEN City1
WHEN 2 THEN City2
WHEN 3 THEN City3
END AS City,
CASE T.N
WHEN 1 THEN State1
WHEN 2 THEN State2
WHEN 3 THEN State3
END AS State
FROM YourTable
CROSS JOIN (VALUES(1),(2),(3)) AS T(N)
) T1
WHERE City IS NOT NULL
You can't use an alias (from SELECT
clause) in WHERE
clause because the logical processing order(section: Logical Processing Order of the SELECT statement
) is WHERE
and then SELECT
:
FROM
ON
JOIN
WHERE <--
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT <--
DISTINCT
ORDER BY <--
TOP
But, you can use an alias in ORDER BY
:
SELECT h.SalesOrderID, YEAR(h.OrderDate) OrderYear
FROM Sales.SalesOrderHeader h
ORDER BY OrderYear;
Solutions: see the solutions presented by Mark Byers.
Tibor Karaszi: Why can't we have column alias in ORDER BY?