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

varchar(1) is pointless - it's either 0 or 1 character, but the var part adds at least 2 bytes overhead for that. Any string of 5 chars or less ought to be char(x) - in your case: char(1) — marc_s
– marc_s, Commented May 11, 2012 at 5:29

Damien_The_Unbeliever · Accepted Answer · 2012-05-11 06:30:20Z

2

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

answered May 11, 2012 at 6:30

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shaikh Farooque · Accepted Answer · 2012-05-11 06:01:02Z

1

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.

edited May 11, 2012 at 6:01

answered May 11, 2012 at 5:21

Shaikh Farooque

2,6402 gold badges22 silver badges36 bronze badges

5 Comments

user1080139 Over a year ago

Thanks fo the response.I did as you had told. But got an error..'Incorrect syntax near ','.

Shaikh Farooque Over a year ago

@user1080139, do you want to display the city,descrip as column or the city+' '+descrip will work for you?

Shaikh Farooque Over a year ago

@user1080139, check out this query.

Shaikh Farooque Over a year ago

Is given query fulfills your requirement?

user1080139 Over a year ago

Wow !! Thank you so much for the help Dude..It works. Alhamdhulillah !

Collectives™ on Stack Overflow

Return multiple fields using case in select statement of sql server

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related