I'm using SQL Server 2014 and I have a Pivot Query that runs on my database and works fine. My problem is that the certain fields have NULL in them. I want the NULLS to be replaced by a Zero.
My query stands as follows:
(SELECT [PROPERTYCODE], [MARKET FINAL], [MARCH 2015], [APRIL 2015], [MAY 2015] FROM (SELECT [PropertyCode], [MTH], [Room Nights], [Market FINAL] FROM HOLDINGS2 )m PIVOT (Sum([ROOM NIGHTS]) FOR [MTH] IN ([MARCH 2015], [APRIL 2015], [MAY 2015] )) AS PVTTABLE) ORDER BY [PropertyCode],[Market FINAL]
The output looks like this:
PropertyCode MarketFinal March 2015 April 2015 May 2015 A UK 245 NULL 320
I need the NULLS to be zeroes.
I've tried this on the Pivot statement:
PIVOT (Sum(NULLIF[ROOM NIGHTS],0))
But it doesn't work. I also tried the NULLIF in the first select statement of my query next to [MARCH 2015], [APRIL 2015] AND [MAY 2015] but it still does not work!