动态枢轴MySQL [英] 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

如何在MySQL中实现呢?我需要处理大量记录，所以有人可以给我提供有效的方法吗?

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??

推荐答案

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

这是动态版本

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

这篇关于动态枢轴MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！