in sqlserver, need to select one of two columns, can a case statment do this?

Question

My table:

Users (userID, col1, col2)

I want to make a generic stored procedure, but I need to return EITHER col1 or col2 in a query.

Can I case statement handle this situation?

SELECT userID, col1 FROM Users

OR

SELECT userID, col2 FROM Users

Answer 1

Using CASE:

SELECT t.userid,
       CASE
         WHEN [something to evaluate why to show col1 vs col2 ] THEN
           t.col1
         ELSE
           t.col2
       END
  FROM USERS t

Using COALESCE:

SELECT t.userid,
       COALESCE(t.col1, t.col2)
  FROM USERS t

COALESCE returns the first column value that isn't null, starting from the left.

Answer 2

Yes, as long as you don't mind returning the same column names:

SELECT userID, ArbitraryCol = CASE WHEN @param = 1 then col1 ELSE col2 END
FROM Users

If you need the column headers to change, then you should use the IF statement

IF @param = 1
BEGIN
SELECT userID, col1 FROM Users
END
ELSE
BEGIN
SELECT userID, col2 FROM Users
END

Answer 3

It can, assuming you have a logical expression for when to return col1 instead of col2:

SELECT USERID CASE WHEN USERTYPE='X' THEN COL1 ELSE COL2 END FROM USERS

If col1 and col2 are of different types, you will need to use CAST or CONVERT to convert one to the other.

Answer 4

I think SQL Server 2000/2005 is something like this.

select
   UserID,
   case UserID
      when 1 then Col1
      when 2 then Col2
      else 'boo'
   end as SpecialSituation
from
   Users