MySQL或PHP将行动态转换为列 [英] MySQL or PHP Turning rows into columns dynamically

问题描述

好的，我有一个如下的销售表:

OK, I have a sales table that looks like this:

Prod_ID | Store_ID | Date_Sold  | Num_Sold

105     | 1010     | 2012-09-21 | 50
105     | 1011     | 2012-09-22 | 20
105     | 1012     | 2012-09-22 | 35
............................................
109     | 1010     | 2012-09-21 | 25
109     | 1011     | 2012-09-23 | 15
109     | 1012     | 2012-09-23 | 30

我想创建一个新的表或视图，如下所示:

I would like to create a new table or view that looks like this:

Store_ID | 105 | ... | 109

1010     | 50  | ... | 25
1011     | 20  | ... | 15
1012     | 35  | ... | 30

我不太确定如何做到这一点.我已经看到人们对此进行了硬编码，但是我不想这样做，因为我有50多个不同的Prod_ID，而且它们还在不断变化.有没有办法动态地做到这一点?我将通过PHP在网页上显示此数据，因此也许有一种使用PHP的简便方法?如果这个解释不清楚，请告诉我.

I'm not really sure how to accomplish this. I have seen where people hard code this in, but I don't want to do that as I have more that 50 different Prod_IDs and they are constantly changing. Is there a way to to this dynamically? I am going to be displaying this data on a webpage via PHP so maybe there is an easier way to do it using PHP?? Let me know if this explanation is unclear.

提前谢谢！

推荐答案

在MySQL中，您将需要使用

In MySQL you will need to use a prepared statement to PIVOT the data. Since MySQL does not have a PIVOT function, then you will need to use an aggregate function along with a CASE:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when Prod_ID = ''',
      Prod_ID,
      ''' then Num_Sold end) AS ''',
      Prod_ID, ''''
    )
  ) INTO @sql
FROM  yourtable;

SET @sql = CONCAT('SELECT store_id, ', @sql, ' 
                  FROM yourtable 
                   GROUP BY store_id');

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

请参见带有演示的SQL提琴

如果您知道已知的列数，则将对类似于以下内容的值进行硬编码:

If you had a known number of columns, then you would hard-code the values similar to this:

select store_id,
  sum(case when prod_id = 105 then Num_Sold end) as '105',
  sum(case when prod_id = 109 then Num_Sold end) as '109'
from yourtable
group by store_id

请参见带有演示的SQL提琴

这篇关于MySQL或PHP将行动态转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！