Dealing with NULL values when using PIVOT

TLDR: You can use ISNULL when dealing with Pivoted data.

I was working with data reflecting feedback our customers have given on how well we did in 3 categories. A customer can provide any combination of those categories (i.e. just 1, 2 out of 3, or all 3). In order to make the data easier to work with for my internal customer, I decided to pivot the feedback and expose each category as a column.

However, we needed to account for NULL values, as the consuming tool apparently doesn't handle NULL data very well. Luckily, I discovered the following is completely possible and valid:

Code (TSQL):

SELECT
    foo
    , ISNULL([lorem], '') AS lorem
    , ISNULL([ipsum], '') AS ipsum
FROM
    table1
    PIVOT
    (
        AVG(foo) FOR bar IN ([lorem],[ipsum])
    ) AS pivottable