How can I SELECT multiple columns within a CASE WHEN on SQL Server

Question

I have searched this site extensively but cannot find a solution   Here is the example of my query   SELECT     ActivityID      Hours    CASE                  WHEN ActivityTypeID  lt  gt  2 THEN                      FieldName    Some Aggregate Sub Query                        FieldName2    Some other aggregate sub query                  WHEN ActivityTypeID   2 THEN                      FieldName    Some Aggregate Sub Query with diff result                        FieldName2    Some Other Aggregate Sub Query with diff result             END    obviously I m leaving out a lot of the query  I just wanted to see if it s possible   I know I probably could just do the  CASE  twice but figured I would ask

User · Accepted Answer

The problem is that the CASE statement won t work in the way you re trying to use it   You can only use it to switch the value of one field in a query   If I understand what you re trying to do  you might need this   SELECT     ActivityID     FieldName   CASE                    WHEN ActivityTypeID  lt  gt  2 THEN                        Some Aggregate Sub Query                    ELSE                       Some Aggregate Sub Query with diff result                 END     FieldName2   CASE                   WHEN ActivityTypeID  lt  gt  2 THEN                        Some Aggregate Sub Query                    ELSE                       Some Aggregate Sub Query with diff result                 END

User · Answer

Actually you can do it   Although  someone should note that repeating the CASE statements are not bad as it seems  SQL Server s query optimizer is smart enough to not execute the CASE twice so that you won t get any performance hit because of that    Additionally  someone might use the following logic to not repeat the CASE  if it suits you     INSERT INTO dbo T1       Col1      Col2      Col3   SELECT     1      SUBSTRING MyCase MergedColumns  0  CHARINDEX      MyCase MergedColumns        SUBSTRING MyCase MergedColumns  CHARINDEX      MyCase MergedColumns    1  LEN MyCase MergedColumns  - CHARINDEX      MyCase MergedColumns   FROM     dbo T1 t LEFT OUTER JOIN       SELECT CASE WHEN 1   1 THEN  2 3  END MergedColumns   AS MyCase ON 1   1   This will insert the values  1  2  3  for each record in the table T1  This uses a delimiter     to split the merged columns  You can write your own split function depending on your needs  e g  for handling null records or using complex delimiter for varchar fields etc    But the main logic is that you should join the CASE statement and select from the result set of the join with using a split logic

User · Answer

No  CASE is a function  and can only return a single value   I think you are going to have to duplicate your CASE logic   The other option would be to wrap the whole query with an IF and have two separate queries to return results   Without seeing the rest of the query  it s hard to say if that would work for you

User · Answer

Case  can return single value only  but you can use complex type   create type foo as  a int  b text   select  case 1 when 1 then  1  qq    foo else  2  ww    foo end

[sql-server] How can I SELECT multiple columns within a CASE WHEN on SQL Server?

Examples related to sql-server

Examples related to select

Examples related to case-when