Convertendo dados de linha em coluna no Mysql dinamicamente

Question

Apr 04, 2018, 03:01 AM

Convertendo dados de linha em coluna no Mysql dinamicamente

Eu escrevi consulta como

    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

E obteve resultado como

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

E eu espero resultado como

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

Eu me referiConsulta de tabela dinâmica MySQL com colunas dinâmicas site para fazer esta consulta

Por favor responda ... Agradecemos antecipadamente