Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

Dealing with NULL values when using PIVOT

Discussion in 'SQL Server Scripts and Tools' started by Peter Schmitz, Jan 31, 2017.

  1. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    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
    Last edited: May 16, 2017

Share This Page