Two or more results of one CASE statement in SQL

Question

Is it possible to SELECT value of two or more columns with one shot of CASE statement? I mean instead of:

select 
 ColumnA = case when CheckColumn='condition' then 'result1' end
,ColumnB = case when CheckColumn='condition' then 'result2' end

Something like:

select case when CheckColumn='condition' then ColumnA='result1', ColumnB='result2' end

UPDATE
Just the same as we can do with the UPDATE statement:

update CTE
set ColumnA='result1', ColumnB='result2'
where CheckColumn='condition'

@Kaf for the clearance of code when the stress is on condition, not on the results. — Przemyslaw Remin, Jul 13 '15 at 09:38
@Fireblade For the performance. Doesn't it check the same condition many times? I looked for the logic of update of multiple columns with one condition in an UPDATE statement http://stackoverflow.com/a/15881737/1903793 — Przemyslaw Remin, Jul 13 '15 at 09:42
Might be worth classifying this: in the text you say you want to do an `UPDATE` but your examples show `SELECT`. Could you change either the text or the examples to make the question consistent? — LondonRob, Jul 13 '15 at 09:51
It is not possible to do this in a case statement. In your first code block you set the value of each column separately using a case statement which is of course correct. But in the second code block you put the case statement directly after the select statement, which is just not allowed. Just use the first option. Or you could go for a solution as posted below using CROSS APPLY or a CTE. — Tom, Jul 13 '15 at 10:03
Do you have only one type of condition to execute update statement or multiple conditions & multiple updates ? — Harsh Baid, Jul 13 '15 at 10:05

score 1 · Answer 1 · answered Jul 13 '15 at 09:24

1

It is not possible with CASE expression. For every column you need new CASE

answered Jul 13 '15 at 09:24

starko

1,150
11
26

score 1 · Answer 2 · answered Jul 13 '15 at 09:32

It is not possible, but you could use a table value constructor as a work around to this, to store each value for columna and columnb against your check column:

SELECT  t.CheckColumn,
        v.ColumnA,
        v.ColumnB
FROM    dbo.YourTable AS t
        LEFT JOIN 
        (VALUES
            ('Condition1', 'Result1', 'Result2'),
            ('Condition2', 'Result3', 'Result4'),
            ('Condition3', 'Result5', 'Result6')
        ) AS v (CheckColumn, ColumnA, ColumnB)
            ON v.CheckColumn = t.CheckColumn;

If you have more complex conditions, then you can still apply this logic, but just use a pseudo-result for the join:

SELECT  t.CheckColumn,
        v.ColumnA,
        v.ColumnB
FROM    dbo.YourTable AS t
        LEFT JOIN 
        (VALUES
            (1, 'Result1', 'Result2'),
            (2, 'Result3', 'Result4'),
            (3, 'Result5', 'Result6')
        ) AS v (ConditionID, ColumnA, ColumnB)
            ON v.ConditionID = CASE WHEN <some long expression> THEN 1
                                    WHEN <some other long expression> THEN 2
                                    ELSE 3
                                END;

score 1 · Answer 3 · answered Jul 13 '15 at 11:35

The equivalent select to the update is:

select 'result1', 'result2'
. . .
where CheckColumn = 'condition';

Your select is different because it produces NULL values. There is an arcane way you can essentially do this with outer apply:

select t2.*
from . . . outer apply
     (select t.*
      from (select 'result1' as col1, 'result2' as col2) t
      where CheckColumn = 'condition'
     ) t2;

This will return NULL values when there is no match. And, you can have as many columns as you would like.

score 0 · Answer 4 · answered Jul 13 '15 at 09:43

What I understood from your question is that you want to update multiple columns if certain condition is true.

For such situation you have to use MERGE statements.

Example of using MERGE is as given on msdn here.

Code example:

-- MERGE statement for update.
USE [Database Name];
GO
MERGE Inventory ity
USING Order ord
ON ity.ProductID = ord.ProductID
WHEN MATCHED THEN
  UPDATE
  SET ity.Quantity = ity.Quantity - ord.Quantity;

More MERGE statement example here.

Shnugo · Answer 5 · 2015-07-13T10:11:02.877

0

You could solve this maybe with a CTE or a CROSS APPLY, somehting like

DECLARE @tbl2 TABLE(inx INT, val1 VARCHAR(10),val2 VARCHAR(10));
INSERT INTO @tbl2 VALUES(1,'value1a','value1b'),(2,'value2a','value2b'),(3,'value2a','value2b');

UPDATE yourTable SET col1=subTable.val1,col2=subTable.val2
FROM yourTable
CROSS APPLY(
    SELECT val1,val2
    FROM @tbl2
    WHERE inx=1 --YourCondition
) AS subTable

edited Jul 13 '15 at 10:11

answered Jul 13 '15 at 09:55

Shnugo

66,100
9
53
114

Two or more results of one CASE statement in SQL

5 Answers5