如何在 MySQL 中动态地将列逆透视为行？

将动态列转换为行

将列转换为行（通常称为“逆透视”）可能是数据重塑中的一项有价值的技术。通过将具有多列的宽表转换为具有较少列的窄表，可以更轻松地分析和处理数据。

MySQL 中的动态逆透视

在 MySQL 中，没有内置的 UNPIVOT 功能。但是，您可以结合使用 SQL 语句和动态 SQL 生成来实现逆透视。

示例：逆透视 Table_1

考虑以下示例，我们要在其中逆透视 Table_1进入 Expected_Result_Table，仅考虑大于的值0:

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

逆透视的动态 SQL 生成

要逆透视 Table_1，我们首先需要根据以下列名称生成动态 SQL 语句表_1。这可以通过以下步骤来实现：

1. 使用以下查询生成不包括“Id”列的不同列名称列表：

   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;

2. 连接生成的SQL字符串形成最终的动态SQL语句：

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

3. 使用以下代码准备并执行动态 SQL 语句：

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

结果：

执行动态SQL语句会生成预期结果表：

---------------------
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

以上是如何在 MySQL 中动态地将列逆透视为行？的详细内容。更多信息请关注PHP中文网其他相关文章！