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

Related question, but NOT a duplicate is http://stackoverflow.com/q/13713316/2420536 — Nameless One, Jun 02 '16 at 10:37

score 37 · Accepted Answer · edited Aug 28 '12 at 09:44

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

score 17 · Answer 2 · edited Aug 28 '12 at 09:44

17

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.

edited Aug 28 '12 at 09:44

Diego

34,802
21
91
134

answered Jan 15 '10 at 15:39

Pete McKinney

1,211
1
11
21

score 3 · Answer 3 · answered Nov 22 '16 at 08:21

3

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

answered Nov 22 '16 at 08:21

Ghost

31
1

3

I think that's possible in Postgres, but not sql-server (which the question is tagged with) – dsz Mar 27 '17 at 01:59
that's clearly and only PostgreSQL, absolutely not M$ SQL Server: https://popsql.com/learn-sql/postgresql/how-to-do-type-casting-in-postgresql/. But nice comment anyway – scavenger Dec 18 '19 at 21:52

score 1 · Answer 4 · answered Aug 07 '17 at 10:45

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.

Thank you for noting it's not a performance issue :) I am just going to use it twice then in my CASE lol — vivasuzi, Apr 06 '20 at 19:59
Nice idea, but it introduces a couple of problems: there might be casting problems (for example, float -> string -> float), you introduce a character that must not appear in the data (and data loves to change over time), the code becomes quite unreadable and I guess that the performance is not that great with all the string processing (think millions of rows). In this case, I'd actually prefer to repeat myself instead of strictly following the DRY principle. — mzuther, Apr 21 '22 at 15:34

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

4 Answers4

Linked