Cara Mengubah Bentuk Tabel untuk Mengubah Baris Menjadi Kolom di MySQL

MySQL adalah salah satu sistem manajemen basis data relasional yang paling populer di dunia. MySQL memungkinkan kita untuk menyimpan, mengelola, dan menganalisis data dalam bentuk tabel. Tabel terdiri dari baris dan kolom yang merepresentasikan entitas dan atribut data. Namun, terkadang kita perlu mengubah bentuk tabel untuk mengubah baris menjadi kolom atau sebaliknya. Hal ini disebut sebagai transposisi tabel.

Transposisi tabel dapat berguna untuk beberapa alasan, seperti:

• Mempermudah visualisasi dan pemahaman data
• Mengoptimalkan kinerja query dan indeks
• Menyesuaikan struktur data dengan kebutuhan bisnis atau aplikasi
• Melakukan operasi agregasi atau perhitungan pada data

Namun, MySQL tidak memiliki fungsi bawaan untuk melakukan transposisi tabel secara langsung. Oleh karena itu, kita perlu menggunakan teknik atau trik tertentu untuk mencapai tujuan ini. Dalam artikel ini, kita akan membahas beberapa cara untuk mengubah bentuk tabel untuk mengubah baris menjadi kolom di MySQL, beserta contoh dan penjelasannya.

Cara 1: Menggunakan Fungsi Agregasi dan Klausa GROUP BY

Salah satu cara untuk mengubah bentuk tabel untuk mengubah baris menjadi kolom di MySQL adalah dengan menggunakan fungsi agregasi dan klausa GROUP BY. Fungsi agregasi adalah fungsi yang mengambil sekumpulan nilai sebagai input dan mengembalikan satu nilai sebagai output. Beberapa fungsi agregasi yang umum digunakan di MySQL adalah:

• SUM(): Menghitung jumlah total dari sekumpulan nilai
• AVG(): Menghitung rata-rata dari sekumpulan nilai
• MIN(): Mengembalikan nilai terkecil dari sekumpulan nilai
• MAX(): Mengembalikan nilai terbesar dari sekumpulan nilai
• COUNT(): Menghitung jumlah nilai yang ada dalam sekumpulan nilai

Klausa GROUP BY adalah klausa yang digunakan untuk mengelompokkan baris yang memiliki nilai yang sama pada satu atau lebih kolom. Klausa GROUP BY biasanya digunakan bersama dengan fungsi agregasi untuk menghitung nilai agregat untuk setiap kelompok.

Untuk mengubah bentuk tabel untuk mengubah baris menjadi kolom dengan menggunakan fungsi agregasi dan klausa GROUP BY, kita perlu melakukan langkah-langkah berikut:

• Tentukan kolom yang akan menjadi baris baru dalam tabel hasil. Kolom ini biasanya merupakan kolom yang memiliki nilai yang berbeda-beda dan tidak terlalu banyak. Misalnya, kolom tahun, bulan, kategori, jenis, dll.
• Tentukan kolom yang akan menjadi kolom baru dalam tabel hasil. Kolom ini biasanya merupakan kolom yang memiliki nilai yang sama atau terbatas untuk setiap baris. Misalnya, kolom nama, produk, merek, dll.
• Tentukan fungsi agregasi yang akan digunakan untuk menghitung nilai untuk setiap kombinasi baris dan kolom baru. Fungsi agregasi ini tergantung pada jenis data dan tujuan analisis kita. Misalnya, SUM(), AVG(), MIN(), MAX(), dll.
• Buat query SQL yang menggunakan fungsi agregasi dan klausa GROUP BY untuk menghasilkan tabel hasil yang diinginkan.

Mari kita lihat contoh untuk memahami cara ini lebih baik. Misalkan kita memiliki tabel berikut yang menyimpan data penjualan produk untuk setiap bulan dan kategori:

Tabel ini memiliki tiga kolom: month, category, dan sales. Kita ingin mengubah bentuk tabel ini untuk mengubah baris menjadi kolom, sehingga kita dapat melihat total penjualan untuk setiap kategori dan bulan dalam satu tabel. Tabel hasil yang kita inginkan adalah sebagai berikut:

Untuk mencapai tujuan ini, kita dapat menggunakan cara 1 dengan langkah-langkah berikut:

• Tentukan kolom yang akan menjadi baris baru dalam tabel hasil. Dalam kasus ini, kolom category akan menjadi baris baru, karena memiliki nilai yang berbeda-beda dan tidak terlalu banyak (A, B, C).
• Tentukan kolom yang akan menjadi kolom baru dalam tabel hasil. Dalam kasus ini, kolom month akan menjadi kolom baru, karena memiliki nilai yang sama atau terbatas untuk setiap baris (Jan, Feb, Mar).
• Tentukan fungsi agregasi yang akan digunakan untuk menghitung nilai untuk setiap kombinasi baris dan kolom baru. Dalam kasus ini, kita akan menggunakan fungsi SUM() untuk menghitung total penjualan untuk setiap kategori dan bulan.
• Buat query SQL yang menggunakan fungsi agregasi dan klausa GROUP BY untuk menghasilkan tabel hasil yang diinginkan. Query SQL yang dapat kita gunakan adalah sebagai berikut:

SELECT
  category,
  SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan,
  SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb,
  SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS Mar
FROM
  sales_table
GROUP BY
  category;

Query ini melakukan hal-hal berikut:

• Memilih kolom category dari tabel sales_table
• Menggunakan fungsi SUM() dan CASE untuk menghitung total penjualan untuk setiap kategori dan bulan. CASE adalah pernyataan bersyarat yang mengembalikan nilai berdasarkan kondisi yang diberikan. Dalam query ini, kita menggunakan CASE untuk memeriksa nilai bulan dan mengembalikan nilai penjualan jika sesuai, atau 0 jika tidak. Misalnya, SUM(CASE WHEN month = ‘Jan’ THEN sales ELSE 0 END) akan mengembalikan total penjualan untuk bulan Januari untuk setiap kategori, atau 0 jika tidak ada penjualan untuk bulan tersebut.
• Memberi nama alias untuk setiap kolom baru dengan menggunakan AS. Alias adalah nama alternatif yang dapat kita berikan untuk kolom atau tabel untuk mempermudah penulisan dan pembacaan query. Dalam query ini, kita memberi nama alias untuk setiap kolom baru dengan nama bulan yang sesuai, seperti Jan, Feb, Mar.
• Menggunakan klausa GROUP BY untuk mengelompokkan baris berdasarkan kolom category. Klausa GROUP BY akan membuat satu baris untuk setiap nilai unik pada kolom category, dan menghitung nilai agregat untuk setiap kolom lainnya.

Query ini akan menghasilkan tabel hasil yang kita inginkan, seperti yang ditunjukkan di atas.

Cara 2: Menggunakan Fungsi Pivot

Cara lain untuk mengubah bentuk tabel untuk mengubah baris menjadi kolom di MySQL adalah dengan menggunakan fungsi pivot. Fungsi pivot adalah fungsi yang memungkinkan kita untuk mengubah bentuk tabel dengan memutar data dari baris menjadi kolom atau sebaliknya. Fungsi pivot dapat digunakan untuk melakukan transposisi tabel dengan mudah dan cepat.

Namun, MySQL tidak memiliki fungsi pivot bawaan yang dapat kita gunakan secara langsung. Oleh karena itu, kita perlu menggunakan ekstensi atau plugin tertentu untuk menambahkan fungsi pivot ke MySQL. Salah satu ekstensi yang populer dan sering digunakan adalah Flexviews. Flexviews adalah ekstensi yang menyediakan fungsi pivot, unpivot, dan incremental refresh untuk MySQL. Flexviews dapat diunduh dan diinstal dari situs web resminya: https://github.com/greenlion/swanhart-tools/tree/master/flexviews

Untuk mengubah bentuk tabel untuk mengubah baris menjadi kolom dengan menggunakan fungsi pivot, kita perlu melakukan langkah-langkah berikut:

• Instal dan aktifkan ekstensi Flexviews di MySQL. Ikuti petunjuk instalasi yang diberikan di situs web resminya.
• Buat sebuah tabel sementara yang berisi data yang ingin kita ubah bentuknya. Tabel sementara adalah tabel yang hanya ada selama sesi MySQL berlangsung dan akan dihapus secara otomatis saat sesi berakhir. Tabel sementara dapat dibuat dengan menggunakan perintah CREATE TEMPORARY TABLE. Misalnya, kita dapat membuat tabel sementara dengan nama sales_temp yang berisi data dari tabel sales_table dengan perintah berikut:

CREATE TEMPORARY TABLE sales_temp AS
SELECT * FROM sales_table;

• Buat sebuah tampilan yang berisi definisi fungsi pivot yang ingin kita gunakan. Tampilan adalah objek basis data yang menyimpan hasil dari query SQL sebagai tabel virtual. Tampilan dapat dibuat dengan menggunakan perintah CREATE VIEW. Misalnya, kita dapat membuat tampilan dengan nama pivot_view yang berisi definisi fungsi pivot untuk mengubah bentuk tabel sales_temp dengan perintah berikut:

CREATE VIEW pivot_view AS
SELECT
  FV$Pivot(
    'sales_temp', -- nama tabel sementara yang berisi data
    'category', -- nama kolom yang akan menjadi baris baru
    'month', -- nama kolom yang akan menjadi kolom baru
    'sales', -- nama kolom yang berisi nilai yang akan dihitung
    'SUM', -- nama fungsi agregasi yang akan digunakan
    NULL -- nama kolom tambahan yang akan dimasukkan ke tabel hasil (opsional)
  ) AS pivot_result; -- nama alias untuk hasil fungsi pivot

• Jalankan query SQL untuk memanggil fungsi pivot dari tampilan yang telah dibuat. Query SQL yang dapat kita gunakan adalah sebagai berikut:

SELECT * FROM pivot_view;

Query ini akan menghasilkan tabel hasil yang kita inginkan, seperti yang ditunjukkan di atas.

Cara 3: Menggunakan Fungsi Unpivot dan Pivot

Cara ketiga untuk mengubah bentuk tabel untuk mengubah baris menjadi kolom di MySQL adalah dengan menggunakan fungsi unpivot dan pivot. Fungsi unpivot adalah fungsi yang kebalikan dari fungsi pivot, yaitu mengubah bentuk tabel dengan memutar data dari kolom menjadi baris. Fungsi unpivot dapat digunakan untuk melakukan transposisi tabel dengan cara yang lebih fleksibel dan dinamis.

Namun, MySQL juga tidak memiliki fungsi unpivot bawaan yang dapat kita gunakan secara langsung. Oleh karena itu, kita juga perlu menggunakan ekstensi atau plugin tertentu untuk menambahkan fungsi unpivot ke MySQL. Salah satu ekstensi yang populer dan sering digunakan adalah Flexviews yang telah kita bahas sebelumnya. Flexviews menyediakan fungsi unpivot selain fungsi pivot dan incremental refresh untuk MySQL.

Untuk mengubah bentuk tabel untuk mengubah baris menjadi kolom dengan menggunakan fungsi unpivot dan pivot, kita perlu melakukan langkah-langkah berikut:

• Instal dan aktifkan ekstensi Flexviews di MySQL jika belum dilakukan sebelumnya. Ikuti petunjuk instalasi yang diberikan di situs web resminya
• Buat sebuah tabel sementara yang berisi data yang ingin kita ubah bentuknya, jika belum ada sebelumnya. Tabel sementara dapat dibuat dengan menggunakan perintah CREATE TEMPORARY TABLE. Misalnya, kita dapat membuat tabel sementara dengan nama sales_temp yang berisi data dari tabel sales_table dengan perintah berikut:

CREATE TEMPORARY TABLE sales_temp AS
SELECT * FROM sales_table;

• Buat sebuah tampilan yang berisi definisi fungsi unpivot yang ingin kita gunakan. Tampilan adalah objek basis data yang menyimpan hasil dari query SQL sebagai tabel virtual. Tampilan dapat dibuat dengan menggunakan perintah CREATE VIEW. Misalnya, kita dapat membuat tampilan dengan nama unpivot_view yang berisi definisi fungsi unpivot untuk mengubah bentuk tabel sales_temp dengan perintah berikut:

CREATE VIEW unpivot_view AS
SELECT
  FV$Unpivot(
    'sales_temp', -- nama tabel sementara yang berisi data
    'category', -- nama kolom yang akan tetap menjadi baris
    'month', -- nama kolom baru yang akan menyimpan nama kolom lama
    'sales', -- nama kolom baru yang akan menyimpan nilai kolom lama
    'month, sales' -- nama kolom lama yang akan diubah menjadi baris
  ) AS unpivot_result; -- nama alias untuk hasil fungsi unpivot

• Buat sebuah tampilan lain yang berisi definisi fungsi pivot yang ingin kita gunakan. Tampilan dapat dibuat dengan menggunakan perintah CREATE VIEW. Misalnya, kita dapat membuat tampilan dengan nama pivot_view yang berisi definisi fungsi pivot untuk mengubah bentuk tabel unpivot_view dengan perintah berikut:

CREATE VIEW pivot_view AS
SELECT
  FV$Pivot(
    'unpivot_view', -- nama tampilan yang berisi data
    'category', -- nama kolom yang akan menjadi baris baru
    'month', -- nama kolom yang akan menjadi kolom baru
    'sales', -- nama kolom yang berisi nilai yang akan dihitung
    'SUM', -- nama fungsi agregasi yang akan digunakan
    NULL -- nama kolom tambahan yang akan dimasukkan ke tabel hasil (opsional)
  ) AS pivot_result; -- nama alias untuk hasil fungsi pivot

• Jalankan query SQL untuk memanggil fungsi pivot dari tampilan yang telah dibuat. Query SQL yang dapat kita gunakan adalah sebagai berikut:

SELECT * FROM pivot_view;

Query ini akan menghasilkan tabel hasil yang kita inginkan, seperti yang ditunjukkan di atas.

Kesimpulan

Dalam artikel ini, kita telah membahas beberapa cara untuk mengubah bentuk tabel untuk mengubah baris menjadi kolom di MySQL, beserta contoh dan penjelasannya. Beberapa cara yang dapat kita gunakan adalah:

• Menggunakan fungsi agregasi dan klausa GROUP BY
• Menggunakan fungsi pivot
• Menggunakan fungsi unpivot dan pivot

Setiap cara memiliki kelebihan dan kekurangan masing-masing, tergantung pada struktur data, tujuan analisis, dan preferensi kita. Kita dapat memilih cara yang paling sesuai dengan kebutuhan dan kondisi kita.

Semoga artikel ini bermanfaat dan dapat membantu Anda dalam melakukan transposisi tabel di MySQL. Terima kasih telah membaca artikel ini sampai selesai.