Writing a SQL Query to Generate Box and Whisker Charts in Excel 2016

In Excel 2016, Microsoft added Box and Whisker Charts. With this new feature, Microsoft has published a support document detailing How to Create a Box and Whisker Chart. These instructions are great if we already have data formatted into such an a table, but what if we're pulling data out of a table in SQL Server? Let's build a query that will generate a suitable result set for plugging right into Excel.

First, here's the format that Excel is looking for:

Let's assume we have the following table:

CREATE TABLE TestScores (
  School nvarchar(63) NOT NULL,
  TestScore int NOT NULL
)

We need a way to pivot our data without aggregating any values. We can do this using the ROW_NUMBER function.

SELECT  [School A], [School B], [School C]
FROM    (
  SELECT  School,
          ROW_NUMBER() OVER (PARTITION BY School ORDER BY TestScore DESC) AS R,
          TestScore
  FROM	TestScores
) t
PIVOT( MAX(TestScore) FOR School in ([School A], [School B], [School C]) ) p

This produces the following table (with random dummy data):

From here, copy and paste into Excel and you've got your Box and Whisker Chart: