Return multiple fields using case in select statement of sql server

Question

I have a table tb3 wherein id,name,sal are to be displayed using a SELECT statement and city,descrip fields need to be displayed in the same SELECT statement only when the flag is 'Y'. How do I do this using CASE statements?

      id    name    sal city    descrip flag
       7    john    80000.00    Canada  prog    y
       6    jone    90000.00    NY  test    y
       3    san 70000.00    NY        lead  y
       2    sam 70000.00    Cali    sub n
       1    sally   60000.00    Canada  archi   n
       4    carl    70000.00    SA  plain   n

I need to do something like this.. I know it's wrong , but for a sample please have a look..

       declare @test varchar(1)
       select @test=flag from tb3
       select id,name,case @test
       when 'Y' then select city,descrip from tb3
       when 'n' then 'inactive'
       end as status from tb3

Answer 1

A result set in SQL has a fixed set of columns - you can't vary how many columns there are on a row-by-row basis. If you're wanting something that is either the columns city and descrip or the word inactive, then you'll have to join those two columns together into a single value:

select id,name,
   CASE WHEN flag='Y' then city + ',' + descrip ELSE 'inactive' END as status
from tb3

Or, you can keep them as two columns, and set them as NULL when not appropriate:

select id,name,
   CASE WHEN flag='Y' then city END as city,
   CASE WHEN flag='Y' then descrip END as descrip
from tb3

Answer 2

You can directly use the name of the flag column as following

Updated:

select id,name ,case flag
when 'Y' then (city +' ' +descrip )
when 'N' then 'inactive'
end as status 
from tb3

How can you use @test, because it is varchar(1) variable, it will hold either 'Y' or 'N' which will return only one type of the result. if the last row flag value is 'Y' then all rows will display city,descrip and if last row flag value is 'N' then it will display 'inactive' irrespective of the flag column result for that row.