CODEWITHSUNDEEP
sqlsql-server-2012
Bulbul
Bulbul

Reputation: 157

CASE statement using 2 different columns

Can someone please help me fix the below code?

CASE Table1.Column1   
                WHEN 'a' THEN '1'
CASE Table2.Column2 
                WHEN 'b' THEN '2'
                WHEN 'c' THEN '3'

                ELSE '4'
    END AS [Test],

I am trying to get values in one new column (Test) from Table1.Column1 and Table2.Column2 column. Thanks.

Upvotes: 1

Views: 162

Answers (2)

Maciej Los
Maciej Los

Reputation: 8591

Another way is to create "dictionary" table:

DECLARE @dictTable TABLE(MyReplacement INT, MyKey VARCHAR(10))

INSERT INTO @dictTable (MyReplacement, MyKey)
VALUES(1, 'a'), (2, 'b')...

Then

SELECT t1.<Fields_List>, t2.<Fields_List>, t3.MyReplacement
FROM Table1 AS t1 (INNER|LEFT|RIGHT) JOIN Table2 AS t2 ON t1.Key = t2.ForeignKey
    LEFT JOIN @dictTable  AS t3 ON t1.Column1 = t2.MyKey AND t2.Column2 = t2.MyKey

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

There's another format for CASE that handles this:

CASE WHEN Table1.Column1 = 'a' THEN '1'
     WHEN Table2.Column2 = 'b' THEN '2'
     WHEN Table2.Column2 = 'c' THEN '3'
     ELSE '4'
END AS [Test],

This format also allows for multiple conditions like: WHEN a.col1 = 1 AND b.col1 = 2 THEN 'something', which can be helpful.

Upvotes: 8

Related Questions