How to handle null in case statement in SQL server

Question

5.00/5 (1 vote)

See more:

SELECT Prov.ProvinceName, CASE WHEN P.ProjectTypeID = 2 THEN COUNT(*) END Road,
SUM(CASE WHEN P.ProjectTypeID = 2 THEN PP.Quantity ELSE 0 END) KM,
CASE WHEN P.ProjectTypeID = 1 THEN COUNT(*) END Bridge,
SUM(CASE WHEN P.ProjectTypeID = 1 THEN PP.Quantity ELSE 0 END) RM,
CASE WHEN P.ProjectTypeID = 3 THEN COUNT(*) END Building
FROM Project P
INNER JOIN ProjectPRA PP ON P.ProjectID = PP.ProjectID
INNER JOIN zDistrict D ON P.DistrictID = D.DistrictID
INNER JOIN zProvince Prov ON D.ProvinceID = Prov.ProvinceID
WHERE PP.PRAID=3
GROUP BY P.ProjectTypeID,Prov.ProvinceName

out put
--- I don't want NULL to be repeated

C#

Badakhshan	NULL	0	3	100	NULL
Badakhshan	8	95	NULL	0	NULL
Badghis	NULL	0	2	48	NULL
Badghis	1	15	NULL	0	NULL
Baghlan	NULL	0	2	83	NULL
Baghlan	8	62	NULL	0	NULL
Balkh	2	14	NULL	0	NULL
Bamyan	NULL	0	4	151	NULL
Bamyan	13	116	NULL	0	NULL
Daikundi	NULL	0	5	150	NULL
Daikundi	23	236	NULL	0	NULL
Daikundi	NULL	0	NULL	0	1
Farah	6	44	NULL	0	NULL
Faryab	NULL	0	7	262	NULL
Faryab	11	134	NULL	0	NULL
Ghazni	5	60	NULL	0	NULL
Ghor	NULL	0	1	60	NULL
Ghor	4	77	NULL	0	NULL
Helmand	2	30	NULL	0	NULL
Jowzjan	NULL	0	1	25	NULL
Jowzjan	5	57	NULL	0	NULL
Kabul	NULL	0	2	87	NULL
Kabul	4	25	NULL	0	NULL
Kabul	NULL	0	NULL	0	2
Kandahar	NULL	0	2	370	NULL
Kandahar	2	11	NULL	0	NULL
Khost	NULL	0	10	730	NULL
Khost	1	20	NULL	0	NULL
Kunar	13	126	NULL	0	NULL
Kunduz	NULL	0	1	80	NULL
Kunduz	13	94	NULL	0	NULL
Logar	NULL	0	2	70	NULL
Logar	1	10	NULL	0	NULL
Nengarhar	2	24	NULL	0	NULL
Nimroz	NULL	0	1	70	NULL
Nimroz	5	43	NULL	0	NULL
Nuristan	NULL	0	4	108	NULL
Nuristan	16	166	NULL	0	NULL
Paktika	2	21	NULL	0	NULL
Paktya	NULL	0	9	169	NULL
Paktya	3	45	NULL	0	NULL
Panjsher	NULL	0	1	60	NULL
Panjsher	1	9	NULL	0	NULL
Parwan	NULL	0	2	38	NULL
Samangan	1	13	NULL	0	NULL
Takhar	NULL	0	3	92	NULL
Takhar	8	74	NULL	0	NULL
Urozgan	2	40	NULL	0	NULL
Wardak	NULL	0	1	40	NULL
Wardak	2	16	NULL	0	NULL
Zabul	NULL	0	1	40	NULL
Zabul	1	10	NULL	0	NULL

What I have tried:

I have tried sub query, outer apply, union and different statement in sql case statement

Posted 26-Oct-16 1:10am

Member 10626601

Updated 26-Oct-16 7:16am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rahul_Biswas · Answer 1 · 2016-10-26T07:16:00

Try this:

SQL

SELECT Prov.ProvinceName
	,CASE 
		WHEN P.ProjectTypeID = 2
			THEN COUNT(*)
		--Adding ELSE to cancel out the NULL
		ELSE 0
		END Road
	,SUM(CASE 
			WHEN P.ProjectTypeID = 2
				THEN PP.Quantity
			ELSE 0
			END) KM
	,CASE 
		WHEN P.ProjectTypeID = 1
			THEN COUNT(*)
		--Adding ELSE to cancel out the NULL
		ELSE 0
		END Bridge
	,SUM(CASE 
			WHEN P.ProjectTypeID = 1
				THEN PP.Quantity
			ELSE 0
			END) RM
	,CASE 
		WHEN P.ProjectTypeID = 3
			THEN COUNT(*)
		--Adding ELSE to cancel out the NULL
		ELSE 0		
		END Building
FROM Project P
INNER JOIN ProjectPRA PP ON P.ProjectID = PP.ProjectID
INNER JOIN zDistrict D ON P.DistrictID = D.DistrictID
INNER JOIN zProvince Prov ON D.ProvinceID = Prov.ProvinceID
WHERE PP.PRAID = 3
GROUP BY P.ProjectTypeID
	,Prov.ProvinceName

Wendelius · Answer 2 · 2016-10-26T09:34:00

First of all, if you're running the query in SSMS, the text NULL is just to notify you that the cell has no actual value. If you're going to run the statement in some other program it may show NULL values differently. If you're going to run the statement in your own program, when you receive the NULL values, you can decide how to show them.

Having that said, if you want to modify the output using SQL, one easy way is to use COALESCE[^] function which returns the first non null value. For example the query could look something like

SQL

SELECT Prov.ProvinceName, 
       COALESCE( CASE WHEN P.ProjectTypeID = 2 THEN COUNT(*) END, 0) Road,
       COALESCE( SUM(CASE WHEN P.ProjectTypeID = 2 THEN PP.Quantity ELSE 0 END), 0) KM,
       COALESCE( CASE WHEN P.ProjectTypeID = 1 THEN COUNT(*) END, 0) Bridge,
       COALESCE( SUM(CASE WHEN P.ProjectTypeID = 1 THEN PP.Quantity ELSE 0 END), 0) RM,
       COALESCE( CASE WHEN P.ProjectTypeID = 3 THEN COUNT(*) END, 0) Building
FROM Project P
INNER JOIN ProjectPRA PP ON P.ProjectID = PP.ProjectID
INNER JOIN zDistrict D ON P.DistrictID = D.DistrictID
INNER JOIN zProvince Prov ON D.ProvinceID = Prov.ProvinceID
WHERE PP.PRAID=3 
GROUP BY P.ProjectTypeID,Prov.ProvinceName

The problem is that it's now hard to know if 0 is returned because the COUNT returns 0 or because the value is NULL. One way to tackle this would be to convert the values to character data and use an empty string in case of null, for example

SQL

...
COALESCE( CAST( CASE WHEN P.ProjectTypeID = 1 THEN COUNT(*) END AS varchar(100)), 0) Bridge,
...

But now we're doing excessive formatting and SQL isn't really designed for this...

How to handle null in case statement in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0