I have answered a lot of MySQL pivot questions over on Stack Overflow and a few over on Database Administrators and have learned some things about how to transform data in MySQL.
Unfortunately, MySQL does not have PIVOT
function, so in order to rotate data from rows into columns you will have to use a CASE
expression along with an aggregate function.
Let’s set up some sample data.
|
|
We can easily query the rep, sales, and product data by joining the tables:
|
|
This will give us the data in the format:
REP_NAME | PROD_NAME | SALE_DATE | QUANTITY |
---|---|---|---|
John | Shoes | May, 16 2013 00:00:00+0000 | 20 |
John | Shoes | June, 19 2013 00:00:00+0000 | 2 |
John | Pants | July, 03 2013 00:00:00+0000 | 5 |
John | Shirt | August, 22 2013 00:00:00+0000 | 27 |
But what if we want to see the reps in separate rows with the total number of products sold in each column. This is where we need to implement the missing PIVOT
function, so we’ll use the aggregate function SUM
with conditional logic instead.
|
|
The conditional logic of the CASE
expression works hand in hand with the aggregate function to only get a total of the prod_name
that you want in each column. Since we have 3 products, then you’d write 3 sum(case...
expressions for each column. Here is a demo on SQL Fiddle. This query will give a result of:
REP_NAME | SHOES | PANTS | SHIRT |
---|---|---|---|
Joe | 19 | 148 | 13 |
John | 22 | 5 | 27 |
Sally | 89 | 23 | 650 |
This could easily be rewritten to show the reps in each column and the products in the rows.
|
|
And now the data is reversed:
PROD_NAME | JOHN | SALLY | JOE | BOB |
---|---|---|---|---|
Pants | 5 | 23 | 148 | 0 |
Shirt | 27 | 650 | 13 | 0 |
Shoes | 22 | 89 | 19 | 0 |
As you can see this is a fairly straightforward and easy way to convert rows into columns when you have a limited number of values. We only had 3 products and 4 reps, so we didn’t have a lot of code to write. Things get a bit more complicated when we have an unknown number of columns to transform. If you aren’t going to know the values ahead of time, then you will need to look at using a prepared statement along with dynamic SQL.
When using a prepared statement, you will write a sql string that will then be executed by the server. I always recommend writing a hard-coded version of a query before attempting to write anything dynamically. This will allow you to get the logic correct before doing it with dynamic SQL.
Let’s set up a dynamic query using the data above. You need to report the total quantity of items sold by each rep for each month/year combination. Again, this is easy if you only had a few dates, you would write the query:
|
|
You’d get the result:
REP_NAME | 2013-JANUARY | 2013-FEBRUARY | 2013-MARCH | 2013-APRIL | 2013-MAY |
---|---|---|---|---|---|
Joe | 19 | 0 | 13 | 1 | 0 |
John | 0 | 0 | 0 | 0 | 20 |
Sally | 150 | 23 | 0 | 0 | 89 |
But what happens if you don’t know the dates ahead of time, or you want to pass in certain parameters to filter the dates and make the report flexible. This is where dynamic SQL is needed. In order to create the sql string to execute, you’ll first need to get a full list of the dates from your sales
table. This list will be created by using GROUP_CONCAT()
and CONCAT()
.
|
|
This code creates a full list of all the dates inside of the CASE
expression and the aggregate function.
|
|
Now, your full code using the prepared statement will be:
|
|
Which gives the final result:
REP_NAME | 2013-JANUARY | 2013-FEBRUARY | 2013-MARCH | 2013-APRIL | 2013-MAY | 2013-JUNE | 2013-JULY | 2013-AUGUST |
---|---|---|---|---|---|---|---|---|
Joe | 19 | 0 | 13 | 1 | 0 | 0 | 69 | 78 |
John | 0 | 0 | 0 | 0 | 20 | 2 | 5 | 27 |
Sally | 150 | 23 | 0 | 0 | 89 | 500 | 0 | 0 |
In a few lines of code you’ve got a flexible solution that returns any number of columns.
One note about GROUP_CONCAT()
, MySQL has a default length on a system variable used for concatenation. The system variable is called group_concat_max_len
and the default value is 1024, which means if you have a string that will be longer that 1024, then you will need to alter this variable to allow for a longer string.
These are just a few ways that you can convert rows into columns using MySQL.