Tabel pivot dinamis (mengubah baris menjadi kolom)

Tabel pivot di MySQL dengan header dinamis.

Asumsikan kita memiliki tabel properti – ‘properti’ (skrip untuk pembuatannya disediakan di bawah), dan kita perlu melakukan transformasi data untuk laporan tersebut.

+----+---------+---------------+--------+
| id | item_id | property_name | value  |
+----+---------+---------------+--------+
|  1 |       1 | color         | blue   |
|  2 |       1 | size          | large  |
|  3 |       1 | weight        | 65     |
|  4 |       2 | color         | orange |
|  5 |       2 | weight        | 57     |
|  6 |       2 | size          | large  |
|  7 |       3 | size          | small  |
|  8 |       3 | color         | red    |
|  9 |       3 | weight        | 12     |
| 10 |       4 | color         | violet |
| 11 |       4 | size          | medium |
| 12 |       4 | weight        | 34     |
| 13 |       5 | color         | green  |
| 14 |       5 | weight        | 10     |
+----+---------+---------------+--------+


+---------+--------+--------+--------+
| item_id | color  | size   | weight |
+---------+--------+--------+--------+
|       1 | blue   | large  | 65     |
|       2 | orange | large  | 57     |
|       3 | red    | small  | 12     |
|       4 | violet | medium | 34     |
|       5 | green  | NULL   | 10     |
+---------+--------+--------+--------+

Tidak ada fungsi transformasi tabel otomatis di MySql, seperti yang diketahui (sebagaimana adanya). Kita tidak diragukan lagi dapat menggunakan program (alat) yang terhubung ke MySql dan melakukan manipulasi data. Namun dalam hal ini, kami ingin melakukan rotasi data secara manual, jadi kami memiliki satu opsi: kami dapat membuat kueri yang akan melakukannya.

Pertanyaan ini mungkin dari jenis berikut:

SELECT
  item_id,
  MAX(IF(property_name = 'color', value, NULL)) AS color,
  MAX(IF(property_name = 'size', value, NULL)) AS size,
  ...
  ...
  ...
FROM
  properties
GROUP BY
  item_id;

Seperti yang Anda lihat, kita perlu mengambil tindakan khusus untuk setiap nilai ‘nama properti’. Ketika jenis properti tidak berubah, itu bisa sederhana. Namun bagaimana jika nilai properti di kolom ‘nama properti’ sering berubah atau ditambah dengan yang baru? Dalam hal ini, kueri akan berbeda setiap kali. Dalam hal ini, algoritma pembuatan kueri dinamis dapat membantu kita; algoritma ini harus membaca semua nilai yang mungkin dari kolom ‘nama properti’ dan membuat kueri pada dasarnya. Algoritma konstruksi kueri adalah sebagai berikut:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(property_name = ''',
      property_name,
      ''', value, NULL)) AS ',
      property_name
    )
  ) INTO @sql
FROM properties;
SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');

Sebagai hasilnya akan dibuat kueri:

SELECT
  item_id,
  MAX(IF(property_name = 'color', value, NULL)) AS color,
  MAX(IF(property_name = 'size', value, NULL)) AS size,
  MAX(IF(property_name = 'weight', value, NULL)) AS weight
FROM
  properties
GROUP BY
  item_id

Perlu dicatat bahwa panjang hasil GROUP CONCAT dibatasi pada nilai sistem variabel grup concat max len, yang memiliki nilai default 1024. Jadi, sebelum menggunakan fungsi GROUP CONCAT, nilai ini dapat dibuat lebih tinggi jika Anda memiliki banyak kolom, misalnya:

SET @@group_concat_max_len = 5000;
SELECT GROUP_CONCAT(column_name) FROM table;

Kueri ditulis ke dalam variabel @sql; sekarang kita bisa menjalankannya dengan pernyataan yang sudah disiapkan:

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
+---------+--------+--------+--------+
| item_id | color  | size   | weight |
+---------+--------+--------+--------+
|       1 | blue   | large  | 65     |
|       2 | orange | large  | 57     |
|       3 | red    | small  | 12     |
|       4 | violet | medium | 34     |
|       5 | green  | NULL   | 10     |
+---------+--------+--------+--------+

Script pembuatan dan pengisian tabel:

CREATE TABLE properties (
  id INT(11) NOT NULL AUTO_INCREMENT,
  item_id INT(11) DEFAULT NULL,
  property_name VARCHAR(255) DEFAULT NULL,
  value VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);
INSERT INTO properties VALUES 
  (1, 1, 'color', 'blue'),
  (2, 1, 'size', 'large'),
  (3, 1, 'weight', 65),
  (4, 2, 'color', 'orange'),
  (5, 2, 'weight', 57),
  (6, 2, 'size', 'large'),
  (7, 3, 'size', 'small'),
  (8, 3, 'color', 'red'),
  (9, 3, 'weight', 12),
  (10, 4, 'color', 'violet'),
  (11, 4, 'size', 'medium'),
  (12, 4, 'weight', 34),
  (13, 5, 'color', 'green'),
  (14, 5, 'weight', 10);