SQL Tip: pivoting results

Here’s something I always need to Google: how to pivot the results of a query in T-SQL.

In the Peergroups application, we help people lend objects from their peers. So, simplified, we have a Request table that includes the following example data:

Other people in Peergroups will receive a message from these users, to which they can reply whether or not they have that object. This is the data in the Reply table:

Don’t worry about the exact data. The point is that we have a table with requests and a table with responses. We now want to create a report showing an overview of the requests and the count of the different responses.

My first step was to create a query that groups the responses:

SELECT req.Id, req.CreatedDateTime, req.UserId, req.Description, re.Response, COUNT(re.Response) AS 'Count'
FROM Request req
    LEFT OUTER JOIN Response re ON req.Id = re.RequestId
GROUP BY req.Id, req.CreatedDateTime, req.UserId, req.Description, re.Response
ORDER BY req.CreatedDateTime

This produces the following result:

But, what I want is this:

To achive that, I need to pivot the result. What that means is that you want to make columns from some pieces of a row (i.e. the rows grouped by Yes, No and NotNow responses).

The first thing to do is to take a step back and remove any aggregation (and ordering) from our query:

SELECT req.Id, req.CreatedDateTime, req.UserId, req.Description, re.Response
FROM Request req
    LEFT OUTER JOIN Response re ON req.Id = re.RequestId

This produces are raw data, which we can now wrap in a pivot statement:

SELECT *
FROM
(
    SELECT req.Id, req.CreatedDateTime, req.UserId, req.Description, re.Response
    FROM Request req
        LEFT OUTER JOIN Response re ON req.Id = re.RequestId
) AS SourceTable
PIVOT
(
    COUNT(Response),
    FOR Response IN (Yes, No, NotNow)
) AS PivotTable

So, while the PIVOT statement seems complex, it’s actually quite simple when you write it in several steps:

• write your non-aggregated query to produce the raw data
• surround that by SELECT * FROM ()
• add the PIVOT statement and add your aggregation function there, not forgetting the column values as found in the rows of your source data