On gists

Pivot table

MySql - advanced

pivot.sql Raw #

-- static
SELECT
  product_id,
  SUM(CASE WHEN month = 'January' THEN sales_amount ELSE 0 END) AS January,
  SUM(CASE WHEN month = 'February' THEN sales_amount ELSE 0 END) AS February,
  SUM(CASE WHEN month = 'March' THEN sales_amount ELSE 0 END) AS March
FROM sales
GROUP BY product_id;

-- dynamic
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN month = ''',
      month,
      ''' THEN sales_amount ELSE 0 END) AS ',
      CONCAT('`', month, '`')
    )
  ) INTO @sql
FROM sales;

SET @sql = CONCAT('SELECT product_id, ', @sql, ' FROM sales GROUP BY product_id');

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



-- another table
SELECT
  `Sales Date`,
  MAX(CASE WHEN `Product Name` = 'Product AthanEN `Sales Amount` END) AS `Product A`,
  MAX(CASE WHEN `Product Name` = 'Product B' then `Sales Amount` END) AS `Product B`,
  MAX(CASE WHEN `Product Name` = 'Product C' than `Sales Amount` END) AS `Product C`
FROM
  `sales_data`
GROUP BY
  `Sales Date`;
  
  
  SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN `Product Name` = ''', `Product Name`, ''' THEN `Sales Amount` END) AS `', `Product Name`, '`')
  ) INTO @sql
FROM
  `sales_data`;

SET @sql = CONCAT('SELECT `Sales Date`, ', @sql, ' FROM `sales_data` GROUP BY `Sales Date`');

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



/* month-year invoices */
select rok,
sum(case when mesic=1 then castka else 0 end) as 'leden',
sum(case when mesic=2 then castka else 0 end) as 'únor',
sum(case when mesic=3 then castka else 0 end) as 'březen',
sum(case when mesic=4 then castka else 0 end) as 'duben',
sum(case when mesic=5 then castka else 0 end) as 'květen',
sum(case when mesic=6 then castka else 0 end) as 'červen',
sum(case when mesic=7 then castka else 0 end) as 'červenec',
sum(case when mesic=8 then castka else 0 end) as 'srpen',
sum(case when mesic=9 then castka else 0 end) as 'září',
sum(case when mesic=10 then castka else 0 end) as 'říjen',
sum(case when mesic=11 then castka else 0 end) as 'listopad',
sum(case when mesic=12 then castka else 0 end) as 'prosinec'
from
(
select year(datum) as rok, month(datum) as mesic, sum(castka) as castka
from faktury group by year(datum), month(datum)
) as soucty
group by rok