How to use coalesce with pivot

Question

Hello I'm trying to figure out to use coalesce with a Pivot so I can replace the NULLS with zeroes instead. Here is what I have:

     SELECT * FROM ( SELECT MovieTitle,AwardResultDesc, COALESCE(COUNT(p.AwardResultID),'0') AS T
     FROM tblMovie t1
            INNER JOIN tblAwardDetail p
              ON p.MovieID = t1.MovieID
            INNER JOIN tblAwardResult c
              ON c.AwardResultID = p.AwardResultID
    GROUP BY MovieTitle, AwardResultDesc,p.AwardResultID)

    PIVOT
    (
        max(T) FOR AwardResultDesc IN ('Won' AS "Won",'Nominated' AS "Nominated")
    ) 
    ORDER BY MovieTitle;

It seems like my Pivot is ignoring my coalesce for some reason, any suggestions?

Answer 1

By their nature pivot operations involve aggregate functions, so there's no need to pre-aggregate your data, however, you do need to ensure that the data you are pivoting is sufficiently complete. Here outer joining to tblMovie ensures each movie is returned whether it has any award details or not. If you don't want to return counts for movies that neither won nor received nominations, then use an inner join in place of the outer join:

WITH dta AS (
 SELECT MovieTitle
      , AwardResultDesc
   FROM tblAwardResult ar
   JOIN tblAwardDetail ad
     ON ad.AwardResultID = ar.AwardResultID
  RIGHT JOIN tblMovie m
     ON m.MovieID = ad.MovieID
)
 SELECT *
   FROM dta
   PIVOT( count(*)
     FOR AwardResultDesc IN( 'Won' AS "Won"
                           , 'Nominated' AS "Nominated" ) )
  ORDER BY MovieTitle;