How can I SELECT multiple columns within a CASE WH

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 <> 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...

Answer 1

"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).*;

Answer 2

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.

Answer 3

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 <> 2 THEN
                      (Some Aggregate Sub Query)
                  ELSE
                     (Some Aggregate Sub Query with diff result)
               END,
   FieldName2 = CASE
                  WHEN ActivityTypeID <> 2 THEN
                      (Some Aggregate Sub Query)
                  ELSE
                     (Some Aggregate Sub Query with diff result)
               END

Answer 4

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.