MySQL or PHP Turning rows into columns dynamically

Question

OK, I have a sales table that looks like this:

Prod_ID | Store_ID | Date_Sold  | Num_Sold

105     | 1010     | 2012-09-21 | 50
105     | 1011     | 2012-09-22 | 20
105     | 1012     | 2012-09-22 | 35
............................................
109     | 1010     | 2012-09-21 | 25
109     | 1011     | 2012-09-23 | 15
109     | 1012     | 2012-09-23 | 30

I would like to create a new table or view that looks like this:

Store_ID | 105 | ... | 109

1010     | 50  | ... | 25
1011     | 20  | ... | 15
1012     | 35  | ... | 30

I'm not really sure how to accomplish this. I have seen where people hard code this in, but I don't want to do that as I have more that 50 different Prod_IDs and they are constantly changing. Is there a way to to this dynamically? I am going to be displaying this data on a webpage via PHP so maybe there is an easier way to do it using PHP?? Let me know if this explanation is unclear.

Thanks in advance!!

Query your database and just output the information in an HTML table? — Justin Wood, Oct 03 '12 at 17:41
Looks like this is the same type of problem described here: http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns — Thomas, Oct 03 '12 at 17:43

score 2 · Accepted Answer · answered Oct 03 '12 at 17:40

2

In MySQL you will need to use a prepared statement to PIVOT the data. Since MySQL does not have a PIVOT function, then you will need to use an aggregate function along with a CASE:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when Prod_ID = ''',
      Prod_ID,
      ''' then Num_Sold end) AS ''',
      Prod_ID, ''''
    )
  ) INTO @sql
FROM  yourtable;

SET @sql = CONCAT('SELECT store_id, ', @sql, ' 
                  FROM yourtable 
                   GROUP BY store_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

If you had a known number of columns, then you would hard-code the values similar to this:

select store_id,
  sum(case when prod_id = 105 then Num_Sold end) as '105',
  sum(case when prod_id = 109 then Num_Sold end) as '109'
from yourtable
group by store_id

see SQL Fiddle with Demo

answered Oct 03 '12 at 17:40

Taryn

242,637
56
362
405

Thanks bluefeet! This is exactly what I wanted. Do you happen to know how I would print out the table headers in PHP using this method? I have tried a few different ways but can't seem to get it to work properly. – user1504583 Oct 04 '12 at 13:53
@user1504583 what do you mean by table headers? – Taryn Oct 04 '12 at 13:59
In HTML when you create a new table you use the tag for table headers or column names. I need just the first row across. This way I could use my sorting script by clicking on any table header to see which store is buying the most/least of any given product – user1504583 Oct 04 '12 at 14:47
@user1504583 I am not familiar with how you would do that with PHP. – Taryn Oct 04 '12 at 14:48
OK I got everything to work with a test table, but when I changed it to my real table I get errors. It gives me a warning saying that 1 line(s) were cut by GROUP_CONCAT() and then Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM GROSS_SALES GROUP BY SITE_ID' at line 2. Do you know why this is happening? I am using MySQL Workbench CE for Windows version 5.2.40 revision 8790 – user1504583 Oct 04 '12 at 18:26
Can you create a SQL Fiddle with some working data? http://sqlfiddle.com/ Then I can see the issue. It could be that the line in the `GRUP_CONCAT()` is too long it is limited to 1024 characters. You can expand it here are details -- http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat – Taryn Oct 04 '12 at 18:31
OK I built a Fiddle with 100 rows of data from my real table and it worked just fine. You can see it here: http://sqlfiddle.com/#!8/1c770/1 – user1504583 Oct 04 '12 at 19:00
Does this mean there is a problem with some data in my table somewhere then? – user1504583 Oct 04 '12 at 19:00
@user1504583 how many fields are you trying to rotate to columns? That might be the problem, if your string that you are generating is too long then it might fail. Just the sample you provided the `@sql` string is 566 characters -- http://sqlfiddle.com/#!8/1c770/3 – Taryn Oct 04 '12 at 19:04
Ya, I think that is the problem. I only want to select 3 different fields but there are approximately 70 different FORM_NUMBERs. Is there a work around for this type of issue? Is it possible to SET @sql to a very large variable that won't exceed the limit? – user1504583 Oct 04 '12 at 19:29
@user1504583 see this post -- http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat -- there are details about expanding it – Taryn Oct 04 '12 at 19:32

score 1 · Answer 2 · answered Oct 03 '12 at 17:52

So the view above is the output in MySQL. PHP allows you to lay out the data however you want and will resolve your issue. You would make a repeating row in a table (html for layout, php for repeating) and inside that table put the output of your MySQL statement called through PHP.

There's a good amount of code that goes into this both for setting up the MySQL connection (PHP) and of course laying out your table (HTML).

MySQL or PHP Turning rows into columns dynamically

2 Answers2

Linked