Bagaimana untuk Menyahpivot Lajur secara Dinamik ke dalam Baris dalam MySQL?

Memindahkan Lajur Dinamik ke Baris

Mengalih lajur ke dalam baris, yang sering dirujuk sebagai "menyahpivot," boleh menjadi teknik yang berharga dalam pembentukan semula data . Dengan menukar jadual lebar dengan berbilang lajur kepada jadual sempit dengan lajur yang lebih sedikit, ia menjadi lebih mudah untuk menganalisis dan bekerja dengan data.

Nyah Pivot Dinamik dalam MySQL

Dalam MySQL, tiada fungsi UNPIVOT terbina dalam. Walau bagaimanapun, anda boleh mencapai unpivoting menggunakan gabungan pernyataan SQL dan penjanaan SQL dinamik.

Contoh: Unpivoting Table_1

Pertimbangkan contoh berikut, di mana kita ingin menyahpivot Table_1 ke dalam Jadual_Keputusan_Dijangka, dengan mengambil kira hanya nilai yang lebih besar daripada 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

Penjanaan SQL Dinamik untuk NyahPivot

Untuk menyahpaut Jadual_1, kita perlu menjana pernyataan SQL dinamik berdasarkan nama lajur Jadual_1. Ini boleh dicapai menggunakan langkah berikut:

1. Jana senarai nama lajur yang berbeza tidak termasuk lajur "Id" menggunakan pertanyaan berikut:

   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. Sambungkan rentetan SQL yang dijana untuk membentuk SQL dinamik akhir pernyataan:

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

3. Sediakan dan laksanakan pernyataan SQL dinamik menggunakan kod berikut:

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

Hasil:

Melaksanakan pernyataan SQL dinamik akan menjana Jadual_Keputusan_Dijangka:

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

Atas ialah kandungan terperinci Bagaimana untuk Menyahpivot Lajur secara Dinamik ke dalam Baris dalam MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!