Select multiple columns with single case statement in SQL Server

Question

I have requirement where in value of three columns depend on the value of single column . Say I have a table as

A | B | C | D | E

A,B,C,D and E are the columns and requirement is to get values of A,B and C as such if the value of column D is '1' else the values for A,B and C will be NULL.

I tried using CASE statement but it seems like we have to use CASE multiple times

SELECT 
    CASE 
        WHEN D = '1' 
            THEN A 
            ELSE NULL, 
    CASE 
        WHEN D = '1' 
            THEN B 
            ELSE NULL;

I was looking for something like just one CASE and select the appropriate values. Is there some way we can achieve that or this is the recommended way.

CASE expression; SQL Server doesn't support Switch (Case Statements) — Thom A
– Thom A ♦, Commented Jul 29, 2021 at 17:47
Thank you @Larnu for the response . I will take this into consideration — Harmandeep Singh Kalsi
– Harmandeep Singh Kalsi, Commented Jul 30, 2021 at 19:12

Gordon Linoff · Accepted Answer · 2021-07-29 17:39:31Z

2

Three case expressions is the most natural method. However, you could also use outer apply:

SELECT x.*
FROM t OUTER APPLY
     (SELECT t.a, t.b, t.c
      WHERE d = '1'
     ) x;

answered Jul 29, 2021 at 17:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harmandeep Singh Kalsi Over a year ago

Thank you @GordonLinoff , I will surely try this .

Peter Koltai · Accepted Answer · 2021-07-29 18:47:50Z

-1

If your table has a unique identifier, for example ID, you can also join the table to itself. Assuming your table name is test:

SELECT      test2.A, test2.B, test2.C, test.D, test.E
FROM        test
LEFT JOIN   test test2 ON
                test.ID = test2.ID AND
                test.D = '1';

If you set a UNIQUE composite index on columns ID plus D it can be an efficient query. In the above query you need test.D and test.E to retain original values from these columns, regardless of D's value.

answered Jul 29, 2021 at 18:47

Peter Koltai

10k2 gold badges17 silver badges27 bronze badges

Collectives™ on Stack Overflow

Select multiple columns with single case statement in SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related