/ sql

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:

Id Description UserId CreatedDateTime
1 A lawn mower 54 2018-01-21
2 A ladder 12 2018-01-24

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:

Id RequestId UserId Response
1 2 44 Yes
2 2 112 Yes
3 2 21 No

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:

Id CreatedDateTime UserId Description Response Count
1 2018-01-21 54 A lawn mower NULL NULL
2 2018-01-24 12 A ladder Yes 2
2 2018-01-24 12 A ladder No 1

But, what I want is this:

Id CreatedDateTime UserId Description Yes No NotNow
1 2018-01-21 54 A lawn mower 0 0 0
2 2018-01-24 12 A ladder 2 1 0

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