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

did somebody say pivot?

Paging bluefeet, there is a PIVOT question to be answered.

While that might seem like a joke, it has really happened, especially over on Stack Overflow. If you have seen any of my posts, then the chances are that I was answering a PIVOT question (or something similar). At this time of this post almost 20% of my total answers (over 3k) have been on pivot questions.

You might ask yourself, why pivot? The simple answer is because I love them. I have heard the arguments,1 "don’t do this type of data transformation on a server do it in the application layer", etc. but my feeling is if there is a way to do it then go for it.

While not every database has a PIVOT function, I will answer or attempt to provide a solution on just about any RDBMS.

What’s the point of this post? My goal is to write a series of posts outlining different methods to PIVOT data in a variety of databases. I probably won’t be able to add much more to what is already out there on this topic, but this will be my spin on pivoting because I love it so!!

First up will be MySQL…Stay tuned.

late to the party

Boy, I am sure behind the times with having a tech type of blog but being late is better than never.

I am going to try to use this platform to discuss some of my tech journeys while I delve into my new job as a DBA developer. I have worked on SQL Server in the past but I will be venturing into Oracle development as well as more in depth SQL Server stuff. It should be fun, so I will attempt to post some thoughts here. We will see how it goes!