Pivot Data in MySQL by Month

Question

I have a table with this fields (model, date, price, quantity) I need a SQL (to MySQL) that gives me a dynamic table with the model name, the quantity and the gross sales (price*quantity), but separated by month (date). For example.

            April           |         May          |     June
Model |quantity| Gross Sales|  quantity|Gross Sales| quantity | Gross Sales
  1       6         50           4        40           5            45
  2       10         30          3        9            5            15

Thanks!!

UPDATED:

The table:

    CREATE TABLE `sales` (
      `model` varchar DEFAULT NULL,
      `price` int DEFAULT NULL,
      `quantity` int(2) DEFAULT NULL,
      `date` timestamp
    )

Give us the actual definition of your table, it's really going to help in this instance. — Rich Benner, Jun 30 '16 at 14:38
This is getting to the complexity where I would say: do this on the application level, rather than in a query. — Shadow, Jun 30 '16 at 14:44
Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Shadow, Jun 30 '16 at 14:46
I need do only with SQL I can´t make do this in application — Sergioh Lonet, Jun 30 '16 at 14:48
I gave you the link where a great solution to dynamic pivot in MySQL is described in pure sql. — Shadow, Jun 30 '16 at 15:22

score 0 · Answer 1 · answered Jul 01 '16 at 10:03

This is quite tricky, maybe there's a better way to do it but here's my two cents, note that the grouped column names is impossible in MySQL as there is only the relationship between columns and column names is 1:1.

Below is some code, It generates test data then builds up a procedure that relies on dynamic SQL to generate the query necessary to return the results you require which is also executed dynamically. Note that the code should run without error but DROP statements are commented to prevent unwanted damage to your existing structure. The procedure RANDBETWEEN isn't needed in the final stored procedure but is used for generating test data.

#DROP TABLE IF EXISTS sales;

CREATE TABLE `sales` (
      `model` varchar(64) DEFAULT NULL,
      `price` int DEFAULT NULL,
      `quantity` int(2) DEFAULT NULL,
      `date` datetime
    );

SET @VMinModel := 1;
SET @VMaxModel := 5;

SET @VMinPrice := 10;
SET @VMaxPrice := 100;

SET @VMinQuantity := 1;
SET @VMaxQuantity := 10;

SET @VMinTime := UNIX_TIMESTAMP('2016-01-01');
SET @VMaxTime := UNIX_TIMESTAMP('2016-12-31');

#DROP FUNCTION RANDBETWEEN;

DELIMITER $

CREATE FUNCTION RANDBETWEEN(VMin INTEGER UNSIGNED, VMax INTEGER UNSIGNED) RETURNS INTEGER UNSIGNED
DETERMINISTIC
BEGIN

    RETURN ROUND(RAND() * (VMax - VMin) + VMin);

END$

DELIMITER ;

INSERT INTO `sales` SELECT RANDBETWEEN(@VMinModel, @VMaxModel), RANDBETWEEN(@VMinPrice, @VMaxPrice), RANDBETWEEN(@VMinQuantity, @VMaxQuantity), FROM_UNIXTIME(RANDBETWEEN(@VMinTime, @VMaxTime)) FROM information_schema.COLUMNS LIMIT 1000;

SET @VXDimension := 'model';
SET @VYDimension := 'DATE_FORMAT(date, ''%Y-%m'')';
SET @VMeasures := 'quantity, quantity * price';
SET @VTable := 'sales';

#DROP PROCEDURE IF EXISTS singleDimensionMeltAndCast;

DELIMITER $

CREATE PROCEDURE singleDimensionMeltAndCast (VXDimension CHAR(255), VYDimension CHAR(255), VMeasures CHAR(255), VTable CHAR(255)) 
BEGIN

    SET SESSION group_concat_max_len = 65536;

    DROP TEMPORARY TABLE IF EXISTS YDimensionValues;
    SET @VCreateYDimensionValueTable := CONCAT('CREATE TEMPORARY TABLE YDimensionValues SELECT DISTINCT ', VYDimension, ' AS YDim1 FROM ', VTable, ' ORDER BY ', VYDimension, ' ASC;');

    PREPARE stmt FROM @VCreateYDimensionValueTable;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    DROP TEMPORARY TABLE IF EXISTS Measures;
    CREATE TEMPORARY TABLE Measures (measure CHAR(255));
    SET @VCreateMeasuresTable := CONCAT('INSERT INTO Measures VALUES(''', REPLACE(VMeasures, ', ', '''), ('''), ''')');

    PREPARE stmt FROM @VCreateMeasuresTable;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @VYDimensionSQLFieldsComponent = (SELECT GROUP_CONCAT('SUM(IF(', VYDimension, ' = ''', YDim1, ''', ', measure, ', 0)) `[', YDim1, ' x ',  measure, ']`' SEPARATOR ', ') FROM YDimensionValues, Measures WHERE TRUE);
    SET @VYDimensionSQLGroupComponent = (SELECT GROUP_CONCAT('IF(', VYDimension, ' = ''', YDim1, ''', ', measure, ', 0)' SEPARATOR ', ') FROM YDimensionValues, Measures WHERE TRUE);

    SET @VFinalQuery := CONCAT('SELECT ', VXDimension, ', ', @VYDimensionSQLFieldsComponent, ' FROM ', VTable, ' GROUP BY ', @VYDimensionSQLGroupComponent, ' ORDER BY ', VXDimension, ';');
    PREPARE stmt FROM @VFinalQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$

DELIMITER ;

CALL singleDimensionMeltAndCast(@VXDimension, @VYDimension, @VMeasures, @VTable);

Let me know if you have any questions.

Regards,

James

Pivot Data in MySQL by Month

1 Answers1

Linked