Mengenal Fungsi GROUP_CONCAT() di MySQL

Beberapa hari yang lalu saya mendapat pertanyaan menarik melalui email dari salah satu pengunjung situs Achmatim.Net yang berasal dari Magelang. Si Penanya mengalami kesulitan dalam menampilkan daftar nilai siswa/mahasiswa dalam satu field yang dikelompokkan berdasarkan NIM (nomor induk mahasiswa) dan mata pelajarannya. Mendapat pertanyaan tersebut, saya langsung tertuju ke satu fungsi di MySQL, yaitu GROUP_CONCAT(). Berikut ini pembahasan selengkapnya. Sebagai gambaran, penanya memberikan suatu tabel dan isi datanya sebagai berikut:

NIM    kdMapel    testKe    Nilai
2011001    101    01    8
2011002    101    01    7
2011003    101    01    6
2011001    102    01    9
2011002    102    01    8
2011003    102    01    7
2011001    101    02    7
2011002    101    02    7
2011003    101    02    9
2011001    101    03    8
2011002    101    03    8
2011003    101    03    9
2011001    102    02    7
2011002    102    02    8
2011003    102    02    9

Dan tampilan yang diinginkan adalah sebagai berikut:

NIM    kdMapel    Nilai
2011001 101    8,7,8
2011002 101     7,7,8
2011003 101     6,7,9
2011001 102     9,7
2011002 102     8,8
2011003 102     7,9

Ya, seperti yang sudah saya sebutkan di atas, kita dapat menyelesaikannya dengan mudah menggunakan fungsi GROUP_CONCAT(). Menurut manual MySQL, fungsi ini dapat digunakan untuk menghasilkan suatu string dari beberapa isi record berdasarkan pengelompokkan tertentu. Seperti pada kasus di atas, bahwa yang diinginkan adalah nilai ditampilkan secara mendatar berdasarkan pengelompokkan NIM dan kdMapel.
Sebelum mencoba fungsi GROUP_CONCAT(), kita akan mencoba melakukan grouping terlebih dahulu dengan perintah SQL sebagai berikut:

SELECT nim,kdMapel,nilai FROM tab_ulangan GROUP BY kdMapel, nim

Setelah dijalankan, ternyata hasilnya masih belum sempurna. Memang sudah terjadi pengelompokkan berdasarkan kdMapel dan nim, namun nilai yang ditampilkan hanya satu nilai. Berikut ini hasilnya:

+---------+---------+-------+
| nim     | kdMapel | nilai |
+---------+---------+-------+
| 2011001 | 101     |     8 |
| 2011002 | 101     |     7 |
| 2011003 | 101     |     6 |
| 2011001 | 102     |     9 |
| 2011002 | 102     |     8 |
| 2011003 | 102     |     7 |
+---------+---------+-------+
6 rows in set (0.00 sec)

Nah, dengan menggunakan fungsi GROUP_CONCAT() kita akan menghasilkan tampilan yang berbeda. Perhatikan perintah query berikut ini:

SELECT nim, kdMapel, GROUP_CONCAT(nilai SEPARATOR ',') as nilai 
FROM tab_ulangan GROUP BY kdMapel, nim

dan hasilnya sebagai berikut:

+---------+---------+-------+
| nim     | kdMapel | nilai |
+---------+---------+-------+
| 2011001 | 101     | 8,8,7 |
| 2011002 | 101     | 8,7,7 |
| 2011003 | 101     | 9,6,9 |
| 2011001 | 102     | 9,7   |
| 2011002 | 102     | 8,8   |
| 2011003 | 102     | 7,9   |
+---------+---------+-------+
6 rows in set (0.00 sec)

Mudah bukan??
Jika diinginkan, kita juga bisa melakukan pengurutan nilai tersebut dengan menambahkan ORDER BY. Selain itu, juga kita juga dapat menambahkan DISTINCT untuk mencegah duplikasi data nilai. Berikut ini contoh variasi query yang ditambahkan pengurutan data berdasarkan nilai yang terbesar.

SELECT nim, kdMapel, GROUP_CONCAT(nilai ORDER BY nilai DESC SEPARATOR ',') as nilai 
FROM tab_ulangan GROUP BY kdMapel, nim

Bagaimana hasilnya? Silahkan dicoba sendiri.
Semoga bermanfaat dan maju terus ilmu pengetahuan Indonesia.

Sumber : http://achmatim.net/2011/06/07/mengenal-fungsi-group_concat-di-mysql/

Read More

Export Data Excel ke MySQL

---

Dengan banyaknya pertanyaan di forum mengenai cara transfer data Excel ke MySQL, maka artikel berikut akan memberikan solusi tersebut dengan aplikasi Kettle.

Keuntungan menggunakan Kettle adalah kita tidak perlu membuat tabel di database, pada saat perancangan dengan Kettle hal itu akan sangat mudah dilakukan. Dan Anda mungkin hanya membutuhkan kurang dari 10 menit untuk melakukan kesemua proses transfer data ini, menarik bukan?

Penjelasan langkah demi langkah transfer data ini akan dibagi dua bagian, yaitu membaca data Excel dengan step Excel Input dan memasukkan data ke MySQL dengan step Table Output.

Membaca Data Excel dengan step Excel Input 

1. Download contoh file Excel dari alamat url http://goo.gl/tdMoXz.
2. Jalankan Spoon - aplikasi designer Kettle.
3. Masukkan step Excel Input dari kategori Input ke dalam ruang kerja Spoon.
   
   
4. Klik 2 kali pada step tersebut untuk memunculkan dialog konfigurasi. 
5. Klik tab !Files. 
6. Pada kotak input File or directory,  klik tombol Browse dan arahkan ke file Target Penjualan.xls yang telah Anda download sebelumnya, kemudian tombol Add.
   
   
   
   
7. Klik tombol Show filename(s) untuk memastikan bahwa nama file yang kita masukkan di atas akan terbaca ketika keseluruhan transformation dijalankan. Klik tombol Close.
8. Klik tab !Sheets, dan klik tombol Get sheetname(s). 
9. Akan muncul dialog Enter list yang berisi daftar sheet dari file Excel kita, pilih Target Penjualan dan tekan tombol >.
   
   
   
   Klik tombol OK untuk menutup dialog.
10. Sheet Target Penjualan terlihat telah ditambahkan ke dalam tab Sheets.
    
    
11. Langkah selanjutnya adalah memasukkan nama-nama kolom di dokumen Excel tersebut. Klik tab !Fields, kemudian klik tombol Get fields from header row....
    
    
12. Akan muncul daftar informasi nama kolom dan meta data lainnya.
    
    
13. Klik tombol Preview rows untuk melihat data dari file Excel tersebut. Masukkan jumlah baris data yang ingin dibaca pada dialog Enter Preview Size dan klik tombol OK.
    
    
14. Akan muncul tampilan preview data, klik tombol Close untuk menutupnya.
    
    
15. Klik tombol OK setelah kembali ke dialog step Excel Input.
16. Selesai

Memasukkan Data ke MySQL dengan step Table Output

1. Kembali ke ruang kerja Spoon, tambahkan step Table output dari kategori Output.
   
   
2. Hubungkan kedua step tersebut dengan cara menekan CTRL+Click pada step Excel Input, diikuti CTRL+Click pada Table output. Klik kanan pada step Table output dan pilih New Hop.
   
   
3. Klik 2 kali pada step Table output. Karena kita belum memiliki koneksi MySQL maka klik tombol New... pada kotak Connection untuk membuat koneksi baru ke database MySQL.
4. Pada dialog Database Connection masukkan detil konfigurasi koneksi database MySQL Anda. Jangan lupa untuk memasukkan nama koneksi, pada gambar berikut terlihat nama koneksi yang kita buat adalah Excel_Out.
   
   
5. Klik tombol Test untuk memastikan koneksi tersebut berhasil. Setelah berhasil, klik tombol OK untuk menutup dialog connection.
   
   
6. Kembali ke dialog Table output, masukkan nama tabel yang kita inginkan di MySQL misalkan target_penjualan.
   
   
7. Langkah berikutnya adalah membuat tabel pada database MySQL. Klik tombol SQL.
   
   
8. Akan muncul SQL statement Create ... yang digunakan untuk membuat tabel target_penjualan di database MySQL. Klik tombol execute untuk menjalankan perintah SQL tersebut.
   
   
   
   Setelah selesai klik tombol Close dan OK untuk kembali ke ruang kerja Spoon.
9. Perhatikan di database MySQL kita sudah terbentuk tabel ms_produk.
   
   
10. Simpan file transformation Anda dengan menekan CTRL+S, dan namakan filter tersebut dengan dengan transform_excel_mysql.ktr.
11. Sekarang saatnya untuk populasi data ke tabel tersebut. Pada ruang kerja Spoon klik tombol Run this transformation or job dan kemudian klik Launch.
    
    
12. Tunggu beberapa saat sampai transformation selesai dijalankan.
13. Pada database MySQL, cobalah lihat isi dari tabel target_penjualan. Terlihat data sudah terekspor semua dengan baik.
    
    
14. Selesai.

Referensi : http://pentaho.phi-integration.com/2009/09/export-data-excel-ke-mysql.html

Read More

Menampilkan Nomor Urut pada Hasil Query MySQL dalam Satu Perintah SQL

Artikel pendek untuk Menampilkan Nomor Urut pada Hasil Query MySQL dalam Satu Perintah SQL ini adalah update dari artikel sebelumnya Membuat Nomor Urut dengan MySQL, Nomor Rangking dengan Bahasa SQL.

Artikel ini penulis buat setelah mencoba membuat nomor urut pada Pemrograman Java yang menampilkan data dari Database MySQL menggunakan dua perintah SQL seperti dibahas sebelumnya yaitu :

SET @ranking=0; 
SELECT @ranking:=@ranking+1 AS ranking, nip, nama,
tunjangan from dokter order by tunjangan desc;

Ternyata tidak bekerja, karena Pemrograman seperti Java hanya mau menerima satu baris perintah SQL dalam satu kali eksekusi. Solusinya penulis dapatkan dengan cara menggunakan Satu Perintah SQL berikut ini :

SELECT @ranking:=IFNULL(@ranking,0)+1 AS ranking, nip, nama,
tunjangan from dokter order by tunjangan desc;

Teknik ini sangat bermanfaat ketika anda membutuhkan Nomor urut dalam satu kali perintah SQL, misalnya dalam pembuatan Report seperti Jasper Report di Pemrograman Java / NetBeans, Crystal Report atau Microsoft Reporting dalam Pemrograman VB.NET dan C#.NET atau DataReport dalam Pemrograman VB 6.0.

Kekurangannya dari artikel ini adalah bahasa SQL tersebut belum penulis coba untuk Perintah Query selain Database MySQL, misal SQL Server, PostgreSQL, Oracle, ataupun Firebird/Interbase. Semoga anda bisa mencoba sendiri.

Semoga bermanfaat seperti yang pernah penulis dapatkan :)

 

Referensi : http://cariprogram.blogspot.com/2013/05/menampilkan-nomor-urut-hasil-query-mysql-dalam-satu-perintah-sql.html

Read More

Coalesce Function

The COALESCE function in SQL returns the first non-NULL expression among its arguments.
It is the same as the following CASE statement:
SELECT CASE ("column_name")
  WHEN "expression 1 is not NULL" THEN "expression 1"
  WHEN "expression 2 is not NULL" THEN "expression 2"
  ...
  [ELSE "NULL"]
  END
FROM "table_name"
For examples, say we have the following table,
Table Contact_Info

Name	Business_Phone	Cell_Phone	Home_Phone
Jeff	531-2531	622-7813	565-9901
Laura	NULL	772-5588	312-4088
Peter	NULL	NULL	594-7477

and we want to find out the best way to contact each person according to the following rules:
1. If a person has a business phone, use the business phone number.
2. If a person does not have a business phone and has a cell phone, use the cell phone number.
3. If a person does not have a business phone, does not have a cell phone, and has a home phone, use the home phone number.
We can use the COALESCE function to achieve our goal:
SELECT Name, COALESCE(Business_Phone, Cell_Phone, Home_Phone) Contact_Phone
FROM Contact_Info;
Result:

Name	Contact_Phone
Jeff	531-2531
Laura	772-5588
Peter	594-7477

The COALESCE function in SQL returns the first non-NULL expression among its arguments.
It is the same as the following CASE statement:
SELECT CASE ("column_name")
  WHEN "expression 1 is not NULL" THEN "expression 1"
  WHEN "expression 2 is not NULL" THEN "expression 2"
  ...
  [ELSE "NULL"]
  END
FROM "table_name"
For examples, say we have the following table,
Table Contact_Info

Name	Business_Phone	Cell_Phone	Home_Phone
Jeff	531-2531	622-7813	565-9901
Laura	NULL	772-5588	312-4088
Peter	NULL	NULL	594-7477

and we want to find out the best way to contact each person according to the following rules:
1. If a person has a business phone, use the business phone number.
2. If a person does not have a business phone and has a cell phone, use the cell phone number.
3. If a person does not have a business phone, does not have a cell phone, and has a home phone, use the home phone number.
We can use the COALESCE function to achieve our goal:
SELECT Name, COALESCE(Business_Phone, Cell_Phone, Home_Phone) Contact_Phone
FROM Contact_Info;
Result:

Name	Contact_Phone
Jeff	531-2531
Laura	772-5588
Peter	594-7477

Read More