0

I am trying to create a new column based on date search result.

I have written a code in the link SQL fiddle as I am looking for date automation on each column based on the date format. the date format Coloum can be up to 7 column or less or more, just based on how many dates in the record.

SQL fiddle

Here is a screenshot as I would like to get this similar and the data is based on the number of sold SKU.

enter image description here

  • 1
    Please explain. Do you want a column for each date in the results? Also, what version do you use? – ypercubeᵀᴹ May 3 '18 at 9:54
  • Yes, that is correct, I am looking for a date on the header, let me edit it again to clear explanation. – Steven Smith May 3 '18 at 9:56
  • Sory I'm using MySQL 5.5 with a version of MariaDB 10 – Steven Smith May 3 '18 at 15:55
  • 1
    So you are using both (MariaDB 10 and MySQL 5.5) in different servers? OK. – ypercubeᵀᴹ May 3 '18 at 18:00
  • yep, for now, i am using MySQL in the localhost. – Steven Smith May 4 '18 at 8:29
0

Update status:-

As got advice and helpful from @ypercube

so far I got sorted with Fix static Pivot table but not dynamic Pivot table,

Here is a Static pivot table

    SELECT 
    ls.sku                                AS list_sku, 
    COALESCE( MIN(suo.sku), 'Not Sold' )  AS sold_sku, 
    SUM(CASE suo.date WHEN '2018-04-30' THEN units_ordered ELSE 0 END) AS Monday,
    SUM(CASE suo.date WHEN '2018-05-01' THEN units_ordered ELSE 0 END) AS Tuesday,
    SUM(CASE suo.date WHEN '2018-05-02' THEN units_ordered ELSE 0 END) AS Wednesday,
    SUM(CASE suo.date WHEN '2018-05-03' THEN units_ordered ELSE 0 END) AS Thursday,
    COALESCE( SUM(suo.units_ordered), 0 ) AS total_sold 
 FROM   tbl_list_sku AS ls
       LEFT JOIN tbl_sku_units_order AS suo 
              ON suo.sku = ls.sku 
GROUP  BY ls.sku 
ORDER  BY total_sold DESC ;

Here is output results

enter image description here

So far I will update this answers when I got a fix with Dynamic table

Update: Got fix with Dynamic Pivot Table and please with it

    SET
   @sql = NULL;
SELECT
   GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(suo.date = ''', date, ''', suo.units_ordered, 0)) AS ', CONCAT("`", date, "`") ) ) INTO @sql 
FROM
   tbl_sku_units_order;
SET
   @sql = CONCAT('SELECT ls.sku AS "List SKU", 
 COALESCE( MIN(suo.sku), "Not Sold" ) AS "sold_sku", ', @sql, ' , COALESCE( SUM(suo.units_ordered), 0 ) AS total_sold 
FROM
   tbl_list_sku AS ls 
   LEFT JOIN
      tbl_sku_units_order AS suo 
      ON suo.sku = ls.sku 
GROUP BY
   ls.sku 
ORDER BY
   total_sold DESC');
 PREPARE stmt 
FROM
   @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is a result

Pivot Table Result

I know I'm not great code in the world, but delight with results and thank for your help @ypercube.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.