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中的每一行变成结果表中的一列.如果在表B中添加了id = 1的新条目,那么我希望结果表自动扩展一列以容纳此额外的数据点.
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
来模拟数据透视. 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演示
- SQLFiddle Demo
如果您有多个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演示(另一个示例)
- SQLFiddle Demo
- SQLFiddle Demo (Another example)
两个查询的输出:
╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║ 1 ║ P ║ S ║
║ 2 ║ R ║ Q ║
╚════╩═══════╩═══════╝
这篇关于MySQL查询将行动态转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!