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. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 createtableproducts(prod_idintnotnull,prod_namevarchar(50)notnull,primarykey(prod_id));insertintoproducts(prod_id,prod\name)values(1,'Shoes'),(2,'Pants'),(3,'Shirt');createtablereps(rep_idintnotnull,rep_namevarchar(50)notnull,primarykey(rep_id));insertintoreps(rep_id,rep_name)values(1,'John'),(2,'Sally'),(3,'Joe'),(4,'Bob');createtablesales(prod_idINTNOTNULL,rep_idINTNOTNULL,sale_datedatetimenotnull,quantityintnotnull,PRIMARYKEY(prod_id,rep_id,sale_date),FOREIGNKEY(prod_id)REFERENCESproducts(prod_id),FOREIGNKEY(rep_id)REFERENCESreps(rep_id));insertintosales(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:

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?

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.

Just a little blurb…I’m a former web and application developer, and Community Manager who is currently working as a Database Reliability Engineer at Stack Overflow. When I’m not at my desk, I’m probably running or pretending to be a woodworker. I can be reached via email at taryn.e.pratt (SHIFT+2) gmail (period) com*. * If you are human, you should be able to decipher the email address.