Using Aliases in Where Clause or an Alternative Option?

by

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

More Related Contents:

  1. Stored procedure hangs seemingly without explanation
  2. to select all the employees who have joined most recently under king
  3. Retrieving last record in each group from database – SQL Server 2005/2008
  4. How to calculate age in T-SQL with years, months, and days
  5. How to check if a column exists in a SQL Server table
  6. How to generate a range of numbers between two numbers?
  7. Convert multiple rows into one with comma as separator [duplicate]
  8. Get first day of week in SQL Server
  9. SET versus SELECT when assigning variables?
  10. Generate Dates between date ranges
  11. T-SQL: Selecting rows to delete via joins
  12. How to print VARCHAR(MAX) using Print Statement?
  13. Check if a row exists, otherwise insert
  14. How to update Identity Column in SQL Server?
  15. Concatenate values based on ID
  16. Splitting delimited values in a SQL column into multiple rows
  17. TSQL: How to convert local time to UTC? (SQL Server 2008)
  18. Select a Column in SQL not in Group By
  19. t-sql get all dates between 2 dates [duplicate]
  20. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  21. How does this CASE expression reach the ELSE clause?
  22. TransactSQL to run another TransactSQL script
  23. How to convert number of minutes to hh:mm format in TSQL?
  24. How can I group time by hour or by 10 minutes?
  25. SQL Server equivalent to MySQL enum data type?
  26. T-SQL – function with default parameters
  27. How do I move a table into a schema in T-SQL
  28. Using IF ELSE statement based on Count to execute different Insert statements
  29. Repeat Rows N Times According to Column Value
  30. String.Format like functionality in T-SQL?

Leave a Comment