试试这个：

Try this:

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

首先，如果你在SSMS中运行查询，文本NULL只是通知你单元格没有实际价值。如果您要在其他程序中运行该语句，则可能会以不同方式显示NULL值。如果您要在自己的程序中运行该语句，当您收到NULL值时，您可以决定如何显示它们。



话虽如此，如果您想使用SQL修改输出，一种简单的方法是使用 COALESCE [ ^ ]函数返回第一个非空值。例如，查询可能类似于

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

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

问题是现在很难知道是否返回0，因为COUNT返回0或因为该值为NULL。解决这个问题的一种方法是将值转换为字符数据，并在null的情况下使用空字符串，例如

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

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

但现在我们正在做过多的格式化和SQL isn真的是为此设计的......

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