SELECT p.*
FROM (SELECT CONVERT(CHAR(10), li.RequestResolutionDate,110) AS DeliveryDate,
ISNULL(ity.ItemTypeCode,'N/A') AS 'ItemTypeCode', rc.ResolutionCodeDescription
FROM LALocationItemTypeRequest li
LEFT JOIN LAItem it ON li.ItemID = it.ItemID
LEFT JOIN LAItemType ity ON it.ItemTypeID = ity.ItemTypeID
LEFT JOIN LAStop st ON li.StopID = st.StopID
LEFT JOIN LALocation lo ON st.LocationID = lo.LocationID
LEFT JOIN LAResolutionCode rc ON li.ResolutionCodeID = rc.ResolutionCodeID
WHERE li.RequestResolutionDate IS NOT NULL) d
PIVOT(COUNT(d.ItemTypeCode) FOR d.ItemTypeCode IN ([18R],[35R],[65],[95],[18],[N/A])) p
ORDER BY YEAR(p.DeliveryDate), p.DeliveryDate
I'm wondering if it is possible to change the N/A value to the 95 and then have all the values of N/A set to 0