user2462471 user2462471 - 4 months ago 9
SQL Question

How to do pivoting with MySQL?

I have a MySQL table:

enter image description here

I want output something like below (kind of pivoting), value in Pen Pencil and Glue column has to be populated from the recent timestamp.

enter image description here

Answer

You can achieve your result by doing a pivot of a subquery which identifies the latest records for each Category - Product group.

SELECT t1.Category,
       MAX(CASE WHEN t1.Product = 'pen'    THEN CONCAT(t1.Flag, '(productid-', t1.Product_ID, ')') ELSE NULL END) AS Pen,
       MAX(CASE WHEN t1.Product = 'pencil' THEN CONCAT(t1.Flag, '(productid-', t1.Product_ID, ')') ELSE NULL END) AS Pencil,
       MAX(CASE WHEN t1.Product = 'glue'   THEN CONCAT(t1.Flag, '(productid-', t1.Product_ID, ')') ELSE NULL END) AS Glue
FROM yourTable t1
INNER JOIN
(
    SELECT Category, Product, MAX(timestamp) AS timestamp
    FROM yourTable
    GROUP BY Category, Product
) t2
    ON t1.Category  = t2.Category AND
       t1.Product   = t2.Product AND
       t1.timestamp = t2.timestamp
GROUP BY t1.Category

Follow the link below for a running demo:

SQLFiddle