CODEWITHSUNDEEP
mysql
Hasnain
Hasnain

Reputation: 99

Replace NULL with a some string

I have a table in my database which looks like:

+-------------+---------+--------+
| ProductName | Status  | Branch |
+-------------+---------+--------+
| P1          | dead    |      1 |
| P1          | dead    |      2 |
| P2          | expired |      1 |
+-------------+---------+--------+

I want to show the result after comparison of products as:

+-------------+---------+--------+
| ProductName | Branch 1|Branch 2|
+-------------+---------+--------+
| P1          | dead    |    dead|
| P2          | expired |    OK  |
+-------------+---------+--------+

After I run the following query:

<pre>
SELECT  ProductName,
MAX(CASE 
WHEN Branch=1 
THEN Status
ELSE NULL 
END) AS 'Branch 1',
MAX(CASE 
WHEN Branch=2
THEN Status
ELSE NULL 
END) AS 'Branch 2'
FROM    MyTable
GROUP BY ProductName;

I get result as:

+-------------+---------+--------+
| ProductName | Branch 1|Branch 2|
+-------------+---------+--------+
| P1          | dead    |    dead|
| P2          | expired |    NULL|  
+-------------+---------+--------+

SQLFiddle
I want to replace NULL with OK but couldn't manage to get the desired result. Kindly point me in the right direction. Thanks

Upvotes: 1

Views: 48

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Use COALESCE() to escape null

SELECT  ProductName,
      COALESCE(  MAX(CASE 
            WHEN Branch=1 
            THEN Status
            ELSE NULL 
        END) ,'OK') AS 'Branch 1',
       COALESCE( MAX(CASE 
            WHEN Branch=2
            THEN Status
            ELSE NULL 
        END),'OK') AS 'Branch 2'
FROM    MyTable
GROUP BY ProductName;

DEMO

Upvotes: 3

Related Questions