CODEWITHSUNDEEP
sqlsql-servert-sqlselectcase
Harmandeep Singh Kalsi
Harmandeep Singh Kalsi

Reputation: 3345

Select multiple columns with single case statement in SQL Server

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.

Upvotes: 0

Views: 990

Answers (2)

Peter Koltai
Peter Koltai

Reputation: 9754

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.

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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;

Upvotes: 2

Related Questions