将动态列转置为行

2021-11-20 00:00:00 mysql unpivot

我想知道如何将 Table_1 反透视为 Expected_Result_Table:

I'd like to know how to unpivot Table_1 into Expected_Result_Table:

Table1
-----------------------------------------
Id       abc  brt ccc ddq eee fff gga hxx
-----------------------------------------
12345     0    1   0   5   0   2   0   0  
21321     0    0   0   0   0   0   0   0   
33333     2    0   0   0   0   0   0   0   
41414     0    0   0   0   5   0   0   1   
55001     0    0   0   0   0   0   0   2   
60000     0    0   0   0   0   0   0   0 
77777     9    0   3   0   0   0   0   0


Expected_Result_Table
---------------------
Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

那么，如何转置 Table_1 中的列，从而得到 Expected_Result_Table，只考虑 > 0 的值?

So, How to transpose columns in Table_1 resulting in Expected_Result_Table, considering only values > 0?

推荐答案

MySQL 没有 UNPIVOT 功能，但您可以使用 UNION ALL 将列转换为行.

MySQL does not have an UNPIVOT function, but you can convert your columns into rows using a UNION ALL.

基本语法是:

select id, word, qty
from
(
  select id, 'abc' word, abc qty
  from yt
  where abc > 0
  union all
  select id, 'brt', brt
  from yt
  where brt > 0
) d
order by id;

在您的情况下，您声明您需要动态列的解决方案.如果是这种情况，那么您将需要使用准备好的语句来生成动态 SQL:

In your case, you state that you need a solution for dynamic columns. If that is the case, then you will need to use a prepared statement to generate dynamic SQL:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as word, ',
      c.column_name,
      ' as qty 
      from yt 
      where ',
      c.column_name,
      ' > 0'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
where c.table_name = 'yt'
  and c.column_name not in ('id')
order by c.ordinal_position;

SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');


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

参见SQL Fiddle with Demo

相关文章