set variable result in case statement to be used again in the same select statement

Question

Is it possible to create, inside a single select statement, like this:

SELECT  
   name,  
   CASE  
      WHEN name = 'a' THEN 'a'  
      ELSE 'b'  
   END tag,  
   CASE  
      WHEN tag = 'a' THEN 'a2'  
      ELSE tag = 'b2'  
   END tag2  
FROM table_name

My reason is there is a previous column on the single statement where I want to use its case statement result on my other column.

I want to know if it can be done using variables like:

SELECT  
   name,  
   CASE  
      WHEN name = 'a' THEN 'a'  
      ELSE 'b'
   END @result = tag,  
   CASE  @result
      WHEN 'a' THEN 'a2'  
      ELSE 'b2'  
   END tag2  
FROM table_name

I know the second code is incorrect but that is the logic I need. Thanks guys!

Answer 1

You need to either repeat the expression, or derive it in a separate scope, e.g.:

SELECT name, tag, CASE
   WHEN tag = 'a' THEN 'a2' 
   ELSE 'b2' END AS tag2
FROM
(
   SELECT  name, CASE  
        WHEN name = 'a' THEN 'a'  
        ELSE 'b' END AS tag
   FROM dbo.table_name
) AS derived;