如何让这个工作,它的工作没有Where条款,否则与Where子句,我得到明显的错误,但这基本上是需要做的,任何人都知道如何处理这个？
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
解决方法
您不能在WHERE子句中使用别名.重复表达式(凌乱)或将SELECT放在子查询中,然后将WHERE子句放在外部查询中：
SELECT Id,City,State
FROM
(
SELECT
ID,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),(3)) AS T(N)
) T1
WHERE City IS NOT NULL
