If you have a PIVOT query and it isn’t returning the data you expect, what can you do to troubleshoot it? The thing to do is to break it down into the constituent parts. First, lets take a look at a query and see what we can do to help.

If you have this query:

SELECT [1] AS Student1, [2] AS Student2, coalesce([3], 0) AS Student3
FROM 
    (SELECT student_id, amount FROM [a]) as source
    PIVOT (
            SUM(amount) FOR student_id in ([1],[2],[3])
        ) as pv

Step 1 - Do you have any actual data?

If we break this down, the first thing is we select the original data from the table here:

    (SELECT student_id, amount FROM [a]) as source

If you run that query by itself, what data, if any, does it return? If this doesn’t return any data then fix that.

Step 2 - Do you have your columns in the pivoted columns?

The next step is to see if you have the data that pivot expects to be able to fill out the columns. If you look down at the PIVOT part of the query, what happens is we aggregate a column using SUM and use the FOR keyword to tell the PIVOT where to get the list of columns from:

    SUM(amount) FOR student_id in ([1],[2],[3])

This tells the PIVOT statement to look in the student_id column and find any rows which are either a 1, 2 or 3 and these are going to become the columns. Now go back to the query you ran, does that query have those values in the column that you are doing a PIVOT FOR?

If it doesn’t then you will need to put the values that your data does have.

That’s it

That is all there is to debugging why a PIVOT query returns no data.

What about NULLS?

If you have lots of NULLS then you need to check whether the source query has any data for the columns you are specifying. For example, if you have student_id’s 1,2,3 and in the columns you specify FOR student_id in (1, 500, 900) then you will get NULLS for 500 and 900 and will not see values 2 or 3.

Can’t I just say “FOR student_id in (*)”

No but wouldn’t that be a whole load of awesome that would have stopped hours of pain for many :)

What if I don’t know what columns I want?

Then you need to write a dynamic query and execute it using sp_executesql.

Summary

Check that you have the data returned as rows then check the column you are pivoting “FOR” and make sure the column names you have specified match the values in the rows for that specific column.