Tag Archives: rows-to-columns

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:

Continue reading