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. — Thom A, Sep 10 '19 at 08:56
Possible duplicate of [SQL Server : Columns to Rows](https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows) — Kami, Sep 10 '19 at 09:00

Thom A · Accepted Answer · 2019-09-10T09:10:11.800

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

The "operator" is really `cross apply`. `values` is a table constructor. — Gordon Linoff, Sep 10 '19 at 11:19

score 0 · Answer 2 · edited Sep 10 '19 at 08:49

0

Use union all

select subjectid, Geography from table
union all
select subjectid, history from table
union all
select subjectid, Maths from table

edited Sep 10 '19 at 08:49

Dale K

25,246
15
42
71

answered Sep 10 '19 at 08:49

Zaynul Abadin Tuhin

31,407
5
33
63

`UNION` feels overkill here; it'll require 3 scans on the table. – Thom A Sep 10 '19 at 08:53

score 0 · Answer 3 · answered Sep 10 '19 at 08:58

0

You can use UNPIVOT. It shows you all grades row by row. Below code works fine

SELECT * FROM MyTable t
UNPIVOT
(
    [Grade] FOR [Subject] IN ([Geography], [History], [Maths])
) AS u

answered Sep 10 '19 at 08:58

Burak

186
9

CASE expression on multiple columns

3 Answers3