how to rotate rows into columns in MySQL

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.

CREATE TABLE products
(
    prod_id INT NOT NULL,
    prod_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (prod_id)
);
 
INSERT INTO products (prod_id, prod_name)
VALUES (1, 'Shoes'), (2, 'Pants'), (3, 'Shirt');
 
CREATE TABLE reps
(
  rep_id INT NOT NULL,
  rep_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (rep_id)
);
 
INSERT INTO reps (rep_id, rep_name)
VALUES (1, 'John'), (2, 'Sally'), (3, 'Joe'), (4, 'Bob');
 
CREATE TABLE sales
(
  prod_id INT NOT NULL,
  rep_id INT NOT NULL,
  sale_date datetime NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (prod_id, rep_id, sale_date),
  FOREIGN KEY (prod_id) REFERENCES products(prod_id),
  FOREIGN KEY (rep_id) REFERENCES reps(rep_id)
);
 
INSERT INTO sales (prod_id, rep_id, sale_date, quantity)
VALUES 
  (1, 1, '2013-05-16', 20),
  (1, 1, '2013-06-19', 2),
  (2, 1, '2013-07-03', 5),
  (3, 1, '2013-08-22', 27),
  (3, 2, '2013-06-27', 500),
  (3, 2, '2013-01-07', 150),
  (1, 2, '2013-05-01', 89),
  (2, 2, '2013-02-14', 23),
  (1, 3, '2013-01-29', 19),
  (3, 3, '2013-03-06', 13),
  (2, 3, '2013-04-18', 1),
  (2, 3, '2013-08-03', 78),
  (2, 3, '2013-07-22', 69);

We can easily query the rep, sales, and product data by joining the tables:

SELECT 
  r.rep_name,
  p.prod_name,
  s.sale_date,
  s.quantity
FROM reps r
INNER JOIN sales s
  ON r.rep_id = s.rep_id
INNER JOIN products p
  ON s.prod_id = p.prod_id

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.

SELECT 
  r.rep_name,
  SUM(CASE WHEN p.prod_name = 'Shoes' THEN s.quantity ELSE 0 END) AS Shoes,
  SUM(CASE WHEN p.prod_name = 'Pants' THEN s.quantity ELSE 0 END) AS Pants,
  SUM(CASE WHEN p.prod_name = 'Shirt' THEN s.quantity ELSE 0 END) AS Shirt
FROM reps r
INNER JOIN sales s
  ON r.rep_id = s.rep_id
INNER JOIN products p
  ON s.prod_id = p.prod_id
GROUP BY r.rep_name;

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.

SELECT 
  p.prod_name,
  SUM(CASE WHEN r.rep_name = 'John' THEN s.quantity ELSE 0 END) AS John,
  SUM(CASE WHEN r.rep_name = 'Sally' THEN s.quantity ELSE 0 END) AS Sally,
  SUM(CASE WHEN r.rep_name = 'Joe' THEN s.quantity ELSE 0 END) AS Joe,
  SUM(CASE WHEN r.rep_name = 'Bob' THEN s.quantity ELSE 0 END) AS Bob
FROM products p
INNER JOIN sales s
  ON p.prod_id = s.prod_id
INNER JOIN reps r
  ON s.rep_id = r.rep_id
GROUP BY p.prod_name;

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:

SELECT 
  r.rep_name,
  SUM(CASE WHEN Date_format(s.sale_date, '%Y-%M')= '2013-January' THEN s.quantity ELSE 0 END) AS `2013-January`,
  SUM(CASE WHEN Date_format(s.sale_date, '%Y-%M')= '2013-February' THEN s.quantity ELSE 0 END) AS `2013-February`,
  SUM(CASE WHEN Date_format(s.sale_date, '%Y-%M')= '2013-March' THEN s.quantity ELSE 0 END) AS `2013-March`,
  SUM(CASE WHEN Date_format(s.sale_date, '%Y-%M')= '2013-April' THEN s.quantity ELSE 0 END) AS `2013-April`,
  SUM(CASE WHEN Date_format(s.sale_date, '%Y-%M')= '2013-May' THEN s.quantity ELSE 0 END) AS `2013-May`
FROM reps r
INNER JOIN sales s
  ON r.rep_id = s.rep_id
INNER JOIN products p
  ON s.prod_id = p.prod_id
GROUP BY r.rep_name;

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().

SET @SQL = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when Date_format(s.sale_date, ''%Y-%M'') = ''',
      dt,
      ''' then s.quantity else 0 end) AS `',
      dt, '`'
    )
  ) INTO @SQL
FROM
(
  SELECT Date_format(s.sale_date, '%Y-%M') AS dt
  FROM sales s
  ORDER BY s.sale_date
) d;
 
SELECT @SQL;

This code creates a full list of all the dates inside of the CASE expression and the aggregate function.

SUM(CASE WHEN Date_format(s.sale_date, '%Y-%M') = '2013-January' THEN s.quantity ELSE 0 END) AS `2013-January`,
SUM(CASE WHEN Date_format(s.sale_date, '%Y-%M') = '2013-February' THEN s.quantity ELSE 0 END) AS `2013-February`,
SUM(CASE WHEN Date_format(s.sale_date, '%Y-%M') = '2013-March' THEN s.quantity ELSE 0 END) AS `2013-March`,
...

Now, your full code using the prepared statement will be:

SET @SQL = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when Date_format(s.sale_date, ''%Y-%M'') = ''',
      dt,
      ''' then s.quantity else 0 end) AS `',
      dt, '`'
    )
  ) INTO @SQL
FROM
(
  SELECT Date_format(s.sale_date, '%Y-%M') AS dt
  FROM sales s
  ORDER BY s.sale_date
) d;
 
SET @SQL 
  = CONCAT('SELECT r.rep_name, ', @SQL, ' 
            from reps r
            inner join sales s
              on r.rep_id = s.rep_id
            inner join products p
              on s.prod_id = p.prod_id
            group by r.rep_name;');
 
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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.

6 thoughts on “how to rotate rows into columns in MySQL

  1. Matthias

    Hi, thanks for the code, in your example it works great. you are using the sum function.

    but the values I have in the table are not integer . they are date and varchar.

    If use this:

    SELECT
    u.ID,

    (CASE WHEN v.name = ‘GOZ_GENDER’ THEN d.value ELSE ” END) AS Gender,
    (CASE WHEN v.name = ‘GOZ_BIRTHDATE’ THEN d.value ELSE ” END) AS Birthdate,
    (CASE WHEN v.name = ‘GOZ_COUNTRY’ THEN d.value ELSE ” END) AS Country
    FROM wp_users u
    INNER JOIN wp_cimy_uef_data d
    ON u.ID = d.user_id
    INNER JOIN wp_cimy_uef_fields v
    ON d.field_id = v.ID
    GROUP BY u.ID

    I get the follwing result:

    ID Gender Birthdate Country
    1
    8
    47 1974-06-25
    49 1981-02-26
    363 1979-08-21
    3191 22.07.1980

    Gender (male / female) is filled in the database table also country…

    why does not work? What do am I wrong?

    Matthias

    Reply
  2. akhrikas

    hi would u like to help me
    i have query , i want to make dinamicaly as TH2014 TH2015, ….

    below is my query
    select tgr.no_rptka, tgr.get_name_jabatan,
    sum(case when tgr.get_year_rptka=year(tmr.date_rptka) then tgr.jumlah end) as ‘TH2014’,
    sum(case when tgr.get_year_rptka=year(tmr.date_rptka)+1 then tgr.jumlah end) as ‘TH2015’
    from tbl_master_rptka tmr, tbl_get_rptka tgr

    where
    tmr.no_rptka=tgr.no_rptka

    GROUP BY tgr.get_name_jabatan, tgr.no_rptka order by tgr.no_rptka,id_get ASC

    Reply
  3. arun

    SET @SQL = NULL;
    SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
    ‘sum(case when Date_format(s.sale_date, ”%Y-%M”) = ”’,
    dt,
    ”’ then s.quantity else 0 end) AS `’,
    dt, ‘`’
    )
    ) INTO @SQL
    FROM
    (
    SELECT DATE_FORMAT(s.sale_date, ‘%Y-%M’) AS dt
    FROM sales s
    ORDER BY s.sale_date
    ) d;

    SET @SQL
    = CONCAT(‘SELECT r.rep_name, ‘, @SQL, ‘
    from reps r
    inner join sales s
    on r.rep_id = s.rep_id
    inner join products p
    on s.prod_id = p.prod_id
    group by r.rep_name;’);

    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    i am not able fetch this query result in PHP its throwing
    Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *


*