-1

I have one requirement to format data in mySql. My raw data in table look like below enter image description here

I need the output as below enter image description here

For 2nd Product 1 row is missing so, Qty3 and Percentage 3 is 0.

I have tried with below script, but got error

    SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' MAX(IF(pd.Qty = ''',
      Qty,
      ''', pd.Percentage, NULL)) AS ',
      Percentage
    )
  ) INTO @sql
FROM product;


SET @sql = CONCAT('SELECT pd.GrpId
                    , pd.ProductId, ', @sql, ' 
                   FROM product AS pd
                   WHERE pd.GrpId = ''1011''
                   GROUP BY pd.ProductId');

PREPARE stmt FROM @sql;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Error I got is 18:50:28 PREPARE stmt FROM @sql 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 '5.00, MAX(IF(pd.Qty = '20', pd.Percentage, NULL)) AS 12.15, ' at line 2 0.000 sec

user1926138
  • 1,464
  • 7
  • 34
  • 53

1 Answers1

0

Have you tried looking? This is called a Pivot and there are literally so many articles about doing this as well as many other SO questions like this one: Efficiently convert rows to columns in sql server

It will depend on how many columns you want to end up with (i.e. just the 3 posting in your example data? Or how ever many rows there are?). If you need to support an unknown number of rows, you want a Dynamic Pivot but again there are many articles/answers already on this.

Try using google...

Community
  • 1
  • 1
Milney
  • 6,253
  • 2
  • 19
  • 33