MySQL query to consolidate table

Question

Can you help me create a query that will convert the 1st table (left) to the 2nd table (right). I need to consolidate the product codes to the Purchase Order.

PO table:

Answer 1

Here is the query

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
  'count(case when PONumber = ''',
  PONumber,
  ''' then 1 end) AS ',
  replace(PONumber, ' ', '')
 )
) INTO @sql
 from PO;

SET @sql = CONCAT('SELECT pt.ProductCode, ', @sql, ' from PO pt
group by ProductCode');

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

Answer 2

In mysql there is lack o window functions which will be the best thing here.

But for your particular problem this will solve the issue.

SELECT 
    product_code,
    (SELECT count from your_table t2 
        Join t1 on t2.product_code = t1.product_code and t2.po_number = 'PO1') as PO1,
     (SELECT count from your_table t3
        Join t1 on t3.product_code = t1.product_code and t3.po_number = 'PO2') as PO2,
     (SELECT count from your_table t4
        Join t1 on t4.product_code = t1.product_code and t5.po_number = 'PO3') as PO3
From your_table t1

Just use your table name and column names. Unfortunately if you will add po4 you need to add it to the query. If you would use for example Postgres - you would not have this problem.