CODEWITHSUNDEEP
mysql
LearneR
LearneR

Reputation: 2531

Dynamic pivot MySQL

Let's say I have a table that looks like:

ID   |   Item       |   Purchased  
17   |   Chocolate  |       1304       
17   |    Biscuit   |     1209           
17   |     Jelly    |     657            
17   |     Milk     |      2234

I want it to convert such data dynamically into (columns increase dynamically):

ID | Chocolate_Purchased | Biscuit_Purchased | Jelly_Purchased | Milk_Purchased
17   | 1304              | 1209              |  657            |  2234

How can I achieve this in MySQL?? I need to work on very large amounts of records so can somebody please give me the methods that would be efficient??

Upvotes: 0

Views: 133

Answers (1)

Mihai
Mihai

Reputation: 26784

SELECT id,SUM(CASE WHEN Item='Chocolate' THEN Purchased END) as Chocolate_Purchased,
SUM(CASE WHEN Item='Biscuit' THEN Purchased END) as Biscuit_Purchased,
SUM(CASE WHEN Item='Jelly' THEN Purchased END) Jelly_Purchased,
SUM(CASE WHEN Item='Mile' THEN Purchased END) as Mile_Purchased 
GROUP BY id

Here is the dynamic version

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(t.item = ''',
      item,
      ''', t.purchased, 0)) AS ',
      item
    )
   )INTO @sql
FROM t;

SET @sql = CONCAT('SELECT id,
                     ', @sql, ' 
                   FROM t               
                   GROUP BY id');

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

FIDDLE

Upvotes: 2

Related Questions