Mysql查询动态将行转换为列 [英] Mysql query to dynamically convert rows to columns

问题描述

MySQL 能否将列转换为行，动态添加行所需的列.我认为我的问题可能与数据透视表有关，但我不确定，除了给出以下示例之外，我不知道如何构建这个问题.

Can MySQL convert columns into rows, dynamically adding as many columns as are needed for the rows. I think my question might be related to pivot tables but I'm unsure and I don't know how to frame this question other than by giving the following example.

给定两个表 A 和 B，它们看起来像

Given a two tables A and B, which look like

表 A

+--+-----+----+
|id|order|data|
+--+-----+----+
|1 |1    |P   |
+--+-----+----+
|2 |2    |Q   |
+--+-----+----+
|2 |1    |R   |
+--+-----+----+
|1 |2    |S   |
+--+-----+----+

我喜欢编写如下所示的查询:

I like to write a query that looks like the following:

结果表

+--+-----+-----+
|id|data1|data2|
+--+-----+-----+
|1 |P    |S    |
+--+-----+-----+
|2 |R    |Q    |
+--+-----+-----+

基本上我想将表 B 中的每一行变成结果表中的一列.如果为 id=1 向表 B 添加了一个新条目，那么我希望结果表自动扩展一列以容纳这个额外的数据点.

Basically I want to turn each row in table B into a column in the result table. If there was a new entry was added to table B for id=1, then I want the result table to automatically extend by one column to accommodate this extra data point.

推荐答案

你可以使用GROUP BY和MAX来模拟pivot.MySQL 也支持 IF 语句.

You can use GROUP BY and MAX to simulate pivot. MySQL also supports IF statement.

SELECT  ID,
        MAX(IF(`order` = 1, data, NULL)) data1,
        MAX(IF(`order` = 2, data, NULL)) data2
FROM    TableA
GROUP   BY ID

• SQLFiddle 演示

如果您有多个 order 值，动态 SQL 可能更合适，这样您就不必修改查询:

If you have multiple values of order, dynamic SQL may be more appropriate so that you will not have to modify the query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(`order` = ', `order`, ',data,NULL)) AS data', `order`)
  ) INTO @sql
FROM TableName;

SET @sql = CONCAT('SELECT  ID, ', @sql, ' 
                  FROM    TableName
                  GROUP   BY ID');

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

• SQLFiddle 演示
• SQLFiddle 演示(另一个例子)

两个查询的输出:

╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║  1 ║ P     ║ S     ║
║  2 ║ R     ║ Q     ║
╚════╩═══════╩═══════╝

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