Bagaimana untuk Pivot Lajur ke Baris dalam MySQL untuk Pelaporan?

MySQL Pivot: Tukar lajur kepada baris

Dalam pangkalan data hubungan seperti MySQL, data selalunya perlu diubah untuk tujuan pelaporan. Satu transformasi sedemikian melibatkan penukaran lajur kepada baris, satu proses yang dipanggil "pangsi". Teknik ini membolehkan perwakilan data yang lebih fleksibel dan ringkas.

Soalan:

Pertimbangkan jadual MySQL berikut:

<code class="language-sql">CREATE TABLE mytable (
  id INT, 
  month VARCHAR(3), 
  col1 VARCHAR(1), 
  col2 VARCHAR(1), 
  col3 VARCHAR(1), 
  col4 VARCHAR(1)
);

INSERT INTO mytable (id, month, col1, col2, col3, col4) VALUES
(101, 'Jan', 'A', 'B', NULL, 'B'),
(102, 'Feb', 'C', 'A', 'G', 'E');</code>

Matlamatnya ialah untuk membuat laporan yang memaparkan nilai dari col1 hingga col4 sebagai baris dan bulan Jan dan Feb sebagai lajur.

Penyelesaian:

MySQL tidak menyediakan fungsi terbina dalam untuk menyahpangsi atau memutar data. Walau bagaimanapun, kita boleh mensimulasikan operasi ini menggunakan UNION ALL dan agregat dengan ungkapan CASE.

1. Anti-perspektif:

Untuk menyahpaut data, kami mencipta subkueri baharu yang menggunakan UNION ALL untuk menggabungkan baris daripada semua lajur menjadi satu lajur:

<code class="language-sql">SELECT id, month, col1 AS `value`, 'col1' AS `descrip`
UNION ALL
SELECT id, month, col2 AS `value`, 'col2' AS `descrip`
UNION ALL
SELECT id, month, col3 AS `value`, 'col3' AS `descrip`
UNION ALL
SELECT id, month, col4 AS `value`, 'col4' AS `descrip`
FROM mytable;</code>

2. Perspektif:

Seterusnya, kami membalut pertanyaan unpivot dalam subkueri dan menggunakan agregat dan penyata CASE untuk mengubah data ke dalam format yang diperlukan:

<code class="language-sql">SELECT descrip, 
  MAX(CASE WHEN month = 'Jan' THEN `value` ELSE NULL END) AS Jan,
  MAX(CASE WHEN month = 'Feb' THEN `value` ELSE NULL END) AS Feb
FROM (
  SELECT id, month, `value`, descrip
  FROM (
    SELECT id, month, col1 AS `value`, 'col1' AS `descrip`
    UNION ALL
    SELECT id, month, col2 AS `value`, 'col2' AS `descrip`
    UNION ALL
    SELECT id, month, col3 AS `value`, 'col3' AS `descrip`
    UNION ALL
    SELECT id, month, col4 AS `value`, 'col4' AS `descrip`
    FROM mytable
  ) AS unpivoted
) AS src
GROUP BY descrip;</code>

Keputusan:

Descrip	Jan	Feb
col1	A	C
col2	B	A
col3	NULL	G
col4	B	E

Nota: Tukar ELSE 0 END kepada ELSE NULL END untuk menjadikan hasil lebih konsisten dengan spesifikasi pangkalan data dan mengelakkan nilai 0 yang tidak diperlukan.

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