Mysql查询根据两列动态将行转换为列

2022-01-22 00:00:00 pivot pivot-table sql mysql

我在这里关注了一个问题来使用Mysql 查询将行动态转换为列.这很好用，但我需要根据两列进行转换，

I have followed a question here to use a Mysql query to dynamically convert rows to columns. This works fine, but i need to convert this on basis of two columns,

上面链接中提到的查询适用于单列数据"，但我想为数据"和价格"两列工作.

The query mentioned in above link works for a single column "data", but i want to work for two columns which are "data" and "price".

我在这里添加了一个示例，

i have added an example here,

给定一个表格 A，它看起来像

Given a table A, which look like

Table A

|  id|order|data|item|Price|
-----+-----+----------------
|   1|    1|   P| 1  | 50  |
|   1|    1|   P| 2  | 60  |
|   1|    1|   P| 3  | 70  |
|   1|    2|   Q| 1  | 50  |
|   1|    2|   Q| 2  | 60  |
|   1|    2|   Q| 3  | 70  |
|   2|    1|   P| 1  | 50  |
|   2|    1|   P| 2  | 60  |
|   2|    1|   P| 4  | 80  |
|   2|    3|   S| 1  | 50  |
|   2|    3|   S| 2  | 60  |
|   2|    3|   S| 4  | 80  |

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

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

Result Table

|  id|order1|order2|order3|item1|item2|item3|item4|
-----+-----+---------------------------------------
|   1|    P |    Q |      | 50  | 60  | 70  |     |
|   2|    P |      |    S | 50  | 60  |     | 80  |

我尝试创建两个不同的查询，然后创建一个连接来实现这一点，但这可能不是一个好的解决方案.任何人都可以提出与上面链接中提到的相同的解决方案吗?

I have tried to create two different queries and then a join to achieve this, but that may not be a good solution. Can any one suggest a solution same like mentioned in the link above.

谢谢

推荐答案

如果 order 和 item 都有已知数量的值，那么你可以硬编码查询到:

If you had a known number of values for both order and item, then you could hard code the query into:

select id,
  max(case when `order` = 1 then data end) order1,
  max(case when `order` = 2 then data end) order2,
  max(case when `order` = 3 then data end) order3,
  max(case when item = 1 then price end) item1,
  max(case when item = 2 then price end) item2,
  max(case when item = 3 then price end) item3,
  max(case when item = 4 then price end) item4
from tableA
group by id;

参见演示.但是您将遇到的部分问题是因为您正在尝试转换多列数据.我获得最终结果的建议是首先取消数据透视.MySQL 没有 unpivot 函数，但您可以使用 UNION ALL 将多对列转换为行.unpivot 的代码类似于以下内容:

See Demo. But part of the problem that you are going to have is because you are trying to transform multiple columns of data. My suggestion to get the final result would be to unpivot the data first. MySQL does not have an unpivot function but you can use a UNION ALL to convert the multiple pairs of columns into rows. The code to unpivot will be similar to the following:

select id, concat('order', `order`) col,  data value
from tableA
union all
select id, concat('item', item) col, price value
from tableA;

参见演示.结果将是:

| ID |    COL | VALUE |
-----------------------
|  1 | order1 |     P |
|  1 | order1 |     P |
|  1 | order1 |     P |
|  1 |  item1 |    50 |
|  1 |  item2 |    60 |
|  1 |  item3 |    70 |

如您所见，这已经占用了 order/data 和 item/price 的多个列，并且将其转换为多行.完成后，您可以使用带有 CASE 的聚合函数将值转换回列:

As you can see this has taken the multiple columns of order/data and item/price and convert it into multiple rows. Once that is completed, then you can convert the values back into columns using an aggregate function with a CASE:

select id, 
  max(case when col = 'order1' then value end) order1,
  max(case when col = 'order2' then value end) order2,
  max(case when col = 'order3' then value end) order3,
  max(case when col = 'item1' then value end) item1,
  max(case when col = 'item2' then value end) item2,
  max(case when col = 'item3' then value end) item3
from
(
  select id, concat('order', `order`) col,  data value
  from tableA
  union all
  select id, concat('item', item) col, price value
  from tableA
) d
group by id;

参见演示.最后，需要将上面的代码转换成动态的prepared statement查询:

See Demo. Finally, you need to convert the above code into a dynamic prepared statement query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when col = ''',
      col,
      ''' then value end) as `', 
      col, '`')
  ) INTO @sql
FROM
(
  select concat('order', `order`) col
  from tableA
  union all
  select concat('item', `item`) col
  from tableA
)d;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  from
                  (
                    select id, concat(''order'', `order`) col,  data value
                    from tableA
                    union all
                    select id, concat(''item'', item) col, price value
                    from tableA
                  ) d
                  group by id');

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

请参阅 SQL Fiddle 演示.这给出了一个结果:

See SQL Fiddle with demo. This gives a result:

| ID | ORDER1 | ORDER2 | ORDER3 | ITEM1 | ITEM2 |  ITEM3 |  ITEM4 |
-------------------------------------------------------------------
|  1 |      P |      Q | (null) |    50 |    60 |     70 | (null) |
|  2 |      P | (null) |      S |    50 |    60 | (null) |     80 |

相关文章