Converting row data into column in Mysql Dynamically

Question

I have written query as

    SELECT  c.customer_name, o.net_total,
  GROUP_CONCAT(
      'SUM(IF(ps.product_sku_name = ''',
      product_sku_name,
      ''',  o.net_total,0)) AS ',
      product_sku_name

  ) 
FROM products_sku ps
LEFT JOIN products  AS p
                    ON p.product_id = ps.product_id
                    left join order_lines ol on ol.product_sku_id = ps.product_sku_id 
                    left join orders o on o.order_id = ol.order_id
                    left join customers c on c.customer_id = o.customer_id
                   GROUP BY ps.product_sku_id

And got result as

customer_name     net_total    GROUP_CONCAT( 'SUM(IF(ps.product_sku_name = ''', product_sku_name, ''', o.net_total,0)) AS ', product_sku_name )
Customer2         126.0000     SUM(IF(ps.product_sku_name = 'PSKU Name 1',  o.net_total,0)) AS PSKU Name 1,SUM(IF(ps.product_sku_name = 'PSKU Name 1',  o.net_total,0)) AS PSKU Name 1,SUM(IF(ps.product_sku_name = 'PSKU Name 1',  o.net_total,0)) AS PSKU Name 1
Customer1         105.0000     SUM(IF(ps.product_sku_name = 'PSKU Name2',  o.net_total,0)) AS PSKU Name2
NULL              NULL         SUM(IF(ps.product_sku_name = 'PSKU Name3',  o.net_total,0)) AS PSKU Name3
NULL              NULL        SUM(IF(ps.product_sku_name = 'Product SKU 4',  o.net_total,0)) AS Product SKU 4
Customer1         945.0000    SUM(IF(ps.product_sku_name = 'PSKU Name4',  o.net_total,0)) AS PSKU Name4
Customer1         2159.3600   SUM(IF(ps.product_sku_name = 'Jordon',  o.net_total,0)) AS Jordon,SUM(IF(ps.product_sku_name = 'Jordon',  o.net_total,0)) AS Jordon

And i expect result as

customer_name       PSKU Name 1    PSKU Name 2    PSKU Name 3  PSKU Name 4 Jordon    
Customer2           126            NULL           NULL         NULL        NULL
Customer1           NULL           105            NULL         945         2159

I have referred MySQL pivot table query with dynamic columns site for making this query

Please reply ... Thanks in advance

score 1 · Accepted Answer · answered Apr 28 '18 at 23:57

1

Your attempt got part way toward building a query. But you need a stored procedure to build and execute it.

See http://mysql.rjweb.org/doc.php/pivot for such code.

answered Apr 28 '18 at 23:57

Rick James

135,179
13
127
222

Converting row data into column in Mysql Dynamically

1 Answers1