How to pivot record based on week number in mysql?

user_13883723 0
Viewed 233

I can easily pivot the result date wise but unable to do same in week number wise.

The table structure exp.

CREATE TABLE `products` (
  `product_name` varchar(250) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `sales` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `products` */
insert  into `products`(`product_name`,`date`,`sales`) values 
('Micro','2020-08-01',100),
('Micro','2020-08-02',200),
('x','2020-08-02',300),
('y','2020-08-01',300),
('z','2020-08-03',400),
('x','2020-08-03',400),
('y','2020-08-10',500),
('z','2020-08-10',500),
('x','2020-08-10',500),
('y','2020-08-10',500),
('z','2020-08-10',500),
('x','2020-08-10',500),
('y','2020-08-10',500),
('z','2020-08-10',500),
('x','2020-08-11',230),
('y','2020-08-11',210),
('z','2020-08-11',240);

The query for showing sum of sale by date wise as below mysql code i used

SET SESSION group_concat_max_len = 10000;

DROP TEMPORARY TABLE IF EXISTS temp_sale;
CREATE TEMPORARY TABLE temp_sale
SELECT `date`,product_name,SUM(sales) AS sales FROM 
products
WHERE DATE BETWEEN DATE_ADD("2020-08-12",INTERVAL -29 DAY) AND "2020-08-12" 
GROUP BY `date`,product_name;

SELECT * FROM temp_sale;

// this code is used to get the result on date wise pivot
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN date = ''',
      DATE_FORMAT(DATE, '%Y-%m-%d'),
      ''' THEN round(sales,2) end) AS `',
      
      DATE_FORMAT(DATE, '%Y-%m-%d'), '`'
    )
  ) INTO @sql
  
  FROM `temp_sale` WHERE DATE BETWEEN DATE_ADD("2020-08-12",INTERVAL -29 DAY) AND "2020-08-12" ;




SET @final_query = CONCAT('select product_name,',@sql,' from temp_sale
group by product_name');

PREPARE stmt FROM @final_query;
EXECUTE stmt;

and output for the above query like as below

enter image description here

But the problem is that i need to sum the sum of sales record by week wise. like as

Product_name | sales | week-31 | week- 32 | week- 33

Please help here in pivot data by week number dynamically.

pivot mysql
1 Answers

This pivot table makes that what you want without temporary table

CREATE TABLE `products` (
  `product_name` varchar(250) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `sales` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `products` */
insert  into `products`(`product_name`,`date`,`sales`) values 
('Micro','2020-08-01',100),
('Micro','2020-08-02',200),
('x','2020-08-02',300),
('y','2020-08-01',300),
('z','2020-08-03',400),
('x','2020-08-03',400),
('y','2020-08-10',500),
('z','2020-08-10',500),
('x','2020-08-10',500),
('y','2020-08-10',500),
('z','2020-08-10',500),
('x','2020-08-10',500),
('y','2020-08-10',500),
('z','2020-08-10',500),
('x','2020-08-11',230),
('y','2020-08-11',210),
('z','2020-08-11',240);

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN date = ''',
      DATE_FORMAT(DATE, '%Y-%m-%d'),
      ''' THEN round(sales,2) end) AS `',
      
      DATE_FORMAT(DATE, '%Y-%m-%d'), '`'
    )
  ) INTO @sql
  
  FROM `temp_sale` WHERE DATE BETWEEN DATE_ADD("2020-08-12",INTERVAL -29 DAY) AND "2020-08-12" ;

SELECT 
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WEEK(`date`) WHEN "',
     WEEK(`date`),
      '" THEN `sales` ELSE 0 END) AS `',
      CONCAT('week-',WEEK(`date`)), '`'      
    )
    ORDER BY WEEK(`date`)
  ) 
  INTO @sql
FROM products as p

SET @sql = CONCAT('select product_name,SUM(`sales`), ',@sql,' from products
group by product_name');

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


product_name | SUM(`sales`) | week-30 | week-31 | week-32
:----------- | -----------: | ------: | ------: | ------:
Micro        |          300 |     100 |     200 |       0
x            |         1930 |       0 |     700 |    1230
y            |         2010 |     300 |       0 |    1710
z            |         2140 |       0 |     400 |    1740

db<>fiddle here

user_51935360
Related