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
– Justin Wood, Commented Oct 3, 2012 at 17:41
Looks like this is the same type of problem described here: stackoverflow.com/questions/12598120/… — Thomas
– Thomas, Commented Oct 3, 2012 at 17:43

Taryn · Accepted Answer · 2012-10-03 17:40:49Z

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 3, 2012 at 17:40

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

user1504583 Over a year ago

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.

Taryn Over a year ago

@user1504583 what do you mean by table headers?

user1504583 Over a year ago

In HTML when you create a new table you use the <th> 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

Taryn Over a year ago

@user1504583 I am not familiar with how you would do that with PHP.

user1504583 Over a year ago

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

|

sfell77 · Accepted Answer · 2012-10-03 17:52:25Z

1

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).

answered Oct 3, 2012 at 17:52

sfell77

9861 gold badge10 silver badges16 bronze badges

Collectives™ on Stack Overflow

MySQL or PHP Turning rows into columns dynamically

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related