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