CODEWITHSUNDEEP
sqlsql-serversql-server-2012
mHelpMe
mHelpMe

Reputation: 6668

change null to zero in select querying involving a pivot

I am using SQL Server 2012. I have written a select query using a pivot. The query works fine apart from one issue which I can't see to figure out.

In the results some of the prices for POLT are null. I want to change nulls to zeros. I know in a normal select query I could use the isnull function however can't get this to work whilst using a pivot?

;with offPrice as
(select ISIN, Price, PriceSource from tblTempPrices
where SecurityType = 'FP'
and TableCheck = 'PB') select * from offPrice
source pivot (max (price) for PriceSource in ([FSB], [POLT])) as pvt

Upvotes: 0

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If the issue is values in the data that are NULL, then fix this in the CTE before the pivot:

with offPrice as (
      select ISIN, coalesce(Price, 0) as Price, PriceSource
      from tblTempPrices
      where SecurityType = 'FP' and TableCheck = 'PB'
     )
. . .

If the problem is missing values before the pivot, then fix this after the pivot:

with offPrice as (
      select ISIN, Price, PriceSource
      from tblTempPrices
      where SecurityType = 'FP' and TableCheck = 'PB'
     ),
     pvt as (
      select * 
      from offPrice
      source pivot (max (price) for PriceSource in ([FSB], [POLT])) as pvt
     )
select ISIN, coalesce(fsb, 0) as fsb, coalesce(polt, 0) as polt
from pvt;

Upvotes: 2

Related Questions