MySQL transpose row to column header php html table

Question

I have a complete table in MySQL

|Product|Market|Qty|Date
|A      |M1    |1  |01/01/2015
|A      |M2    |1  |01/01/2015
|B      |M1    |1  |01/01/2015
|C      |M3    |1  |01/01/2015
|B      |M1    |1  |01/01/2015
|A      |M2    |1  |01/01/2015

How do i populate them in HTML Table such that

Product|M1|M2|M3|M4
A      |1 |2 |0 |0
B      |2 |0 |0 |0
C      |0 |0 |0 |1

Can anyone help me right direction here. Looked into many MySQL transpose and pivot code but not able to implement it or understand it.

Trying to learn and understand here.

Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Richard St-Cyr, Dec 20 '15 at 20:34
It's bit different, I font need the count i need the sum and the rows that are going as headers is dynamic and i want to populate them in html table in PHP — Sumeet Pujari, Dec 20 '15 at 20:49

score 2 · Accepted Answer · edited May 23 '17 at 10:27

2

The answer is very similar to the MySQL pivot table. Instead of counting the rows, you need to add the quantities.

SELECT
    Product,  
        SUM(
        CASE 
            WHEN Market ='M1' 
            THEN Qty 
            ELSE 0 
        END
    ) AS 'M1',
    SUM(
        CASE 
            WHEN Market ='M2' 
            THEN Qty 
            ELSE 0 
        END
    ) AS 'M2',
    SUM(
        CASE 
            WHEN Market ='M3' 
            THEN Qty 
            ELSE 0 
        END
    ) AS 'M3',
    SUM(
        CASE 
            WHEN Market ='M4' 
            THEN Qty 
            ELSE 0 
        END
    ) AS 'M4',
FROM    table_name
GROUP BY Product;

I have not run the query, but you should be able to make it work.

edited May 23 '17 at 10:27

Community

1
1

answered Dec 20 '15 at 20:58

Richard St-Cyr

970
1
8
14

In that case, you can build the query in a stored procedure or in PHP, You can also used specialized tools. Some are listed in the post I mentioned above. – Richard St-Cyr Dec 20 '15 at 21:14
Can i use the GROUP_CONCAT function inside php like a SQL statement that i used and populate the data in HTML Table? – Sumeet Pujari Dec 20 '15 at 21:41
If you want to prepare this in PHP, I would do it in three steps. First, get the list of Markets, using an SQL query. Second, use PHP to prepare a query similar to the one in the solution, creating as many columns in your results based on the market found in the first query. Third, run the query created in PHP. – Richard St-Cyr Dec 21 '15 at 02:47
http://stackoverflow.com/questions/34393321/populate-html-table-with-mysql-stored-procedure?noredirect=1#comment56528373_34393321 i created a new post with results to populate from stored procedure if you can help , it would be really great – Sumeet Pujari Dec 21 '15 at 10:50

MySQL transpose row to column header php html table

1 Answers1