CASE expression on multiple columns

Question

I have a table with below mentioned columns and values

StudentId | Geography | History | Maths
_______________________________________________

1         | NULL      | 25      | NULL
2         | 20        | 23      | NULL
3         | 20        | 22      | 21

I need the output like below:

StudentId | Subject
___________________________
1         | History
2         | Geography
2         | History
3         | Geography
3         | History
3         | Maths

Wherever the value in subject columns (Geography, History and Maths) is NON NULL, I need the 'subject' value of the recepective column name.

I have an idea to pull it for one column using CASE, but not sure how to do it for multiple columns.

Here is what I tried:

SELECT StudentId, CASE WHEN IsNUll(Geography, '#NULL#') <> '#NULL#' THEN 'Geography'
CASE WHEN IsNUll(History, '#NULL#') <> '#NULL#' THEN 'History'
CASE WHEN IsNUll(Maths, '#NULL#') <> '#NULL#' THEN 'Maths' END Subject
FROM MyTable

Also, if you're checking if a value is NULL, then use IS NULL and IS NOT NULL. There is no benefit to using ISNULL to then return a string, and then check the value of that string. Such logic in a WHERE will actually come at a (significant) cost, as it'll make the query non-SARGable. — Larnu

Larnu Larnu · Accepted Answer · 2019-09-10T08:52:45

You need to normalise your data. You can do this with a VALUES operator:

--Create sample data
WITH YourTable AS(
    SELECT V.StudentID,
           V.[Geography],
           V.History,
           V.Maths    
    FROM (VALUES(1,NULL,25,NULL),
                (2,20,23,NULL),
                (3,20,22,21))V(StudentID,[Geography], History, Maths))
--Solution
SELECT YT.StudentID,
       V.[Subject]
FROM YourTable YT
     CROSS APPLY (VALUES('Geography',YT.[Geography]),
                        ('History',YT.History),
                        ('Maths',YT.Maths))V([Subject],SubjectMark)
WHERE V.SubjectMark IS NOT NULL
ORDER BY YT.StudentID;

DB<>Fiddle

CASE expression on multiple columns

3 Answers