Bagaimana untuk Menyahpivot Lajur Dinamik ke dalam Baris dalam MySQL?

Membuat Baris daripada Lajur Dinamik

Dalam bidang pengurusan data, memanipulasi lajur dan baris selalunya diperlukan. Soalan ini menyelidiki tugas khusus: menukar berbilang lajur dinamik kepada jadual baharu dengan baris yang mengandungi kriteria khusus.

Contoh yang diberikan menggambarkan penyahpivotan Jadual_1, menghasilkan Jadual_Keputusan_Dijangka. Perbezaan utama ialah Expected_Result_Table memaparkan setiap nilai bukan sifar dalam baris yang berasingan.

MySQL tidak mempunyai fungsi UNPIVOT langsung, tetapi had ini boleh diatasi menggunakan pernyataan UNION ALL. Pendekatan asas melibatkan mencipta pernyataan SELECT berasingan untuk setiap lajur, memilih ID, nama lajur sebagai nilai "perkataan" dan nilai lajur sebagai nilai "qty". Pernyataan SELECT ini kemudiannya digabungkan menggunakan UNION ALL.

Contohnya:

SELECT id, 'abc' AS word, abc AS qty
FROM yt
WHERE abc > 0
UNION ALL
SELECT id, 'brt', brt
FROM yt
WHERE brt > 0

Pendekatan ini menjadi lebih kompleks apabila berurusan dengan lajur dinamik. Untuk menangani ini, kenyataan yang disediakan boleh digunakan. Ia membenarkan penjanaan dinamik pernyataan SQL berdasarkan metadata.

Coretan kod berikut menunjukkan pendekatan sedemikian:

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;

Dalam contoh ini, SQL dinamik dibina menggunakan maklumat daripada jadual maklumat_schema.columns. Penyataan stmt yang disediakan kemudiannya digunakan untuk melaksanakan SQL yang dijana secara dinamik, menyahpaut lajur dinamik menjadi baris dengan berkesan.

Dengan melaksanakan pendekatan ini, adalah mungkin untuk mengubah lajur dinamik kepada baris, memenuhi keperluan data tertentu.

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