如何在 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中文網其他相關文章！