CODEWITHSUNDEEP
sqlsql-server-2008group-bycaseconditional-statements
Matt
Matt

Reputation: 273

SQL Replace blank value in case statement

I have two tables:

Student (ks2en, ks2ma, ks2av)

Subject (name)

I have the following conditional statement which depending on the subject lists the values in the student columns. These are then subsequently grouped by their respective student column.

In any combination I would like to replace '' blank values in the result set with 'No KS2'.

Here is my case statement which conditionally selects the appropriate column from the student table base on the subject name in the subject table.

   CASE 
      WHEN COALESCE(Name, '') = 'English' THEN
        [Ks2en]
      WHEN COALESCE(Name, '') = 'Mathematics' THEN
        [Ks2ma]
      ELSE
        [Ks2av]
   END AS KS2

Example data:

ks2en
2a
2a
3a
4c

5a
3a
2a

1c

When the subject name is 'English' then the following results are displayed:

KS2
No KS2
1c
2a
3a
4c
5a

Just to be clear, don't worry about ordering, grouping or the conditional element of the statement as I have got these all working. It's just replacing blank values with 'No KS2' so that it displays as one of the groups of values in the results set as above.

Upvotes: 0

Views: 14261

Answers (1)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

What about:

CASE Name
WHEN 'English' THEN
    CASE WHEN [Ks2en]=NULL OR [Ks2en]='' THEN
        'No KS2'
    ELSE
        [Ks2en]
    END
WHEN 'Mathematics' THEN
    CASE WHEN [Ks2ma]=NULL OR [Ks2ma]='' THEN
        'No KS2'
    ELSE
        [Ks2ma]
    END
ELSE
    CASE WHEN [Ks2av]=NULL OR [Ks2av]='' THEN
        'No KS2'
    ELSE
        [Ks2av]
    END
END AS KS2

Removed inner COALESCE as they were not usefull!

Upvotes: 2

Related Questions