How to pivot a table with n rows in mysql?

Question

If I have 1000 rows I want to become every row to Column, and the rows can be anything.
How I do that in mysql?

with an annoying amount of difficulty, but you may find your answer here: http://stackoverflow.com/questions/29501673/merging-two-vertical-tables-onto-one-horizontal-table/29503368?noredirect=1#comment47164695_29503368 — pala_, Apr 08 '15 at 03:34
Thanks But that doesn't work because is static, If i have 1000 rows it means I have to write 1000 "if" sum(case when t1.name = 'Donkey' then 1 else 0 end) Donkey, — Isbelmont, Apr 08 '15 at 03:37
check the comments on the answer, especially the last one that has a fiddle link in it. it uses a prepared statement to dynamically build a pivot table based on rows in a table. if that isn't sufficient, post your table schema here so there's something for us to work from — pala_, Apr 08 '15 at 03:38
It is the same they write donkey and cat int he first line, that is static, — Isbelmont, Apr 08 '15 at 03:43
No. they aren't the same. It just has multiple queries there. http://sqlfiddle.com/#!9/52a43/2 look from line 9 onwards — pala_, Apr 08 '15 at 03:43
Here's a stored proc to discover the necessary columns and generate the IFs needed: [Pivoting](http://mysql.rjweb.org/doc.php/pivot). — Rick James, Apr 08 '15 at 03:56

score 0 · Answer 1 · edited May 23 '17 at 12:29

Here is a MySQL pivot table query for room bookings by weekday:

SELECT slot 
  , max(if(day=1, concat(subject,' ',room), '')) as day1 
  , max(if(day=2, concat(subject,' ',room), '')) as day2 
  , max(if(day=3, concat(subject,' ',room), '')) as day3 
  , max(if(day=4, concat(subject,' ',room), '')) as day4 
  , max(if(day=5, concat(subject,' ',room), '')) as day5 
from schedule 
group by slot

MAX(...) decides between an entry and a blank (the entry will win if one exists) while the group by lines everything up on the same row. Friendly caution: If more than one entry exists for the same day and time, you will only see the one that is alphabetically "greater".

To see how many classes are scheduled by day for each slot (to check for conflicts) try:

SELECT slot 
  , sum(if(day=1,1,0)) as day1 
  , sum(if(day=2,1,0)) as day2 
  , sum(if(day=3,1,0)) as day3 
  , sum(if(day=4,1,0)) as day4 
  , sum(if(day=5,1,0)) as day5 
from schedule 
group by slot

check here for more sollutions: MySQL pivot table

How to pivot a table with n rows in mysql?

1 Answers1