Crosstab or Whatever that said at MySQL …

Dear All, again MySQL again…hehehehe … simple trick from me that will save your time of work when you using looping in code … yeah code … in Delphi, VB, what ever you create the aplication.

We will trying to create crosstab or whatever you will said … in MySQL …

First, saya punya data sebagai berikut :

Tabel karyawan:
 NIP   | Nama
 ------------------------
 001   | Udin tea
 002   | Budi Kahandap
 003   | Andi Kararangge
 004   | Cecep Gorbacep
 005   | Ono Ontohod 

 Tabel master_absen:
 tipe_absen | deskripsi
 ----------------------
 AL         | ALPA
 SK         | SAKIT
 CT         | CUTI 

 Tabel absen:
 NIP  |     TGL    | tipe_absen
 ------------------------------
 001  | 01/07/2009 | AL
 001  | 02/07/2009 | AL
 002  | 05/07/2009 | CT
 004  | 05/07/2009 | CT
 002  | 06/07/2009 | SK
 003  | 07/07/2009 | SK
 003  | 08/07/2009 | SK
 003  | 09/07/2009 | SK
 003  | 10/07/2009 | SK
 005  | 11/07/2009 | AL
 005  | 12/07/2009 | AL
 005  | 13/07/2009 | AL
 005  | 14/07/2009 | AL

Actually we want to create the result of query like this :

 NIP | NAMA            | AL | CT | SK
 ------------------------------------
 001 | Udin tea        | 2  | 0  | 0
 002 | Budi Kahandap   | 0  | 1  | 0
 003 | Andi Kararangge | 0  | 0  | 4
 004 | Cecep Gorbacep  | 0  | 1  | 0
 005 | Ono Ontohod     | 4  | 0  | 0

Tapi … ada tapinya juga neh …. kalau di master_absen dan di data absen ditambah datanya menjadi :

 tipe_absen | deskripsi
 --------------------------
 AL         | ALPA
 SK         | SAKIT
 CT         | CUTI
 IZ         | IZIN PULANG   <---- tambahan data

kemudian data absen jadi :

 NIP  |     TGL    | tipe_absen
 ------------------------------
 001  | 01/07/2009 | AL
 001  | 02/07/2009 | AL
 002  | 05/07/2009 | CT
 004  | 05/07/2009 | CT
 002  | 06/07/2009 | SK
 003  | 07/07/2009 | SK
 003  | 08/07/2009 | SK
 003  | 09/07/2009 | SK
 003  | 10/07/2009 | SK
 005  | 11/07/2009 | AL
 005  | 12/07/2009 | AL
 005  | 13/07/2009 | AL
 005  | 14/07/2009 | AL
 003  | 20/07/2009 | IZ    <--- tambahan data
 004  | 21/07/2009 | IZ    <--- tambahan data

hasil yang diinginkan jadi :

 NIP | NAMA            | AL | CT | IZ | SK
 -----------------------------------------
 001 | Udin tea        | 2  | 0  | 0  | 0
 002 | Budi Kahandap   | 0  | 1  | 0  | 0
 003 | Andi Kararangge | 0  | 0  | 1  | 4
 004 | Cecep Gorbacep  | 0  | 1  | 1  | 0
 005 | Ono Ontohod     | 4  | 0  | 0  | 0

Nah ada tambahan kolom IZ tuh ….

Sekarang, How to make the simple single query statements ? yeah … query statements …

This below is step … step by step we will describe it …

Kalau kita menggunakan sql query dengan link left join seperti biasa :

select k.nip, k.nama, a.tipe_absen, a.tgl
from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, a.tgl
order by k.nip

we got result like this :

Nah itu baru memunculkan semua data yang ada.

Sekarang kita coba di otak atik sql nya dengan menggunakan power of group_concat dan select concat statement dan menjadi sql kita, untuk mendapatkan hasil berapa banyak si-dia absen dan tidak menghiraukan tanggal berapa saja, dan saya tidak akan menjelaskan untuk group_concat and select concat functions disini, bukan gag mau … tapi cape ngetiknya , seperti dibawah ini :

select concat(
'select k.nip, k.nama',
group_concat(
concat(', sum(if(a.tipe_absen="', m.tipe_absen, '", 1, 0)) As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''),
'from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, m.tipe_absen
order by k.nip, m.tipe_absen') into @Absence_SQL
from master_absen m;
prepare Absences from @Absence_SQL;
execute Absences;

Nah, disini kita sudah menggunakan fitur statement dari MySQL, dan juga return statement ke variable @Absence_SQL dan kita akan lakukan execute Absences untuk mendapatkan resultset seperti dibawah ini :

Tapi … eit … just a moment … we got false data …, jika dilihat … record no 2 dan 3, kan ada 002 untuk Budi Kahandap itu punya status CT dan SK …. gak mungkin untuk memunculkan data seperti ini dan diserahkan kepada yang berwajib … hehehehe

So, we change again the sql structure to meet our requirements … like shown as below :

select concat(
'select T.nip, T.nama',
group_concat(concat(', sum(T.', m.tipe_absen,') As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''), '\n',
'from (select k.nip, k.nama',
group_concat(
concat(', sum(if(a.tipe_absen="', m.tipe_absen, '", 1, 0)) As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''),
'from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, m.tipe_absen
order by k.nip, m.tipe_absen) As T
group by T.nip') into @Absence_SQL
from master_absen m;
prepare Absences from @Absence_SQL;
execute Absences;

Nah, so … after we concatenate the sql statements again .. what we got ???

You may see what we want … as shown below :

Ow, … we got right what we want …. records no 2 has become one line …

ck… ck… ck… just a moment … but how we added the data has been described in beginning of this post ?

Just insert the data at master_absen and data absen … and then … just running again the last sql statements, and you will get the results like this :

Look at the columns … somethin’ differents between this result of query than before  ??? You decide

Dikau friendz bisa mengubah – ubah query diatas sesuai dengan keinginan ….

Sampai jumpa di lain Query Tricky. Oh ya… don’t forget to donate to make this blog live and give another tricky …

Salam DBA,

Man’z