Membuat crosstab query (Posting Jadul)

Umumnya setiap bulan, si bos biasanya minta laporan penjualan bulan kemaren. Dan biasanya jenis laporan yang diinginkan adalah hasil penjumlahan ataupun pengelompokkan berdasarkan kriteria tertentu, sehingga memudahkan dalam membaca laporan tersebut dengan cepat tanpa perlu mengetahui detil transaksi yang cukup banyak.

Dalam istilah database, untuk jenis laporan diatas disebut juga dengan crosstab report. Dan untuk membuat crosstab report maka diperlukan juga crosstab query, seperti apa sih modelnya crosstab query itu ?? Untuk mempermudah penjelasan, sengaja aku buat contoh sederhana, selanjutnya silahkan kembangkan sendiri sesuai dengan kebutuhan di perusahaan anda.

Misalkan ada sebuah toko serbaguna yang menjual beraneka macam dan ragam kebutuhan hidup, seperti pensil, buku dan alat komunikasi HaPe. Silahkan liat tabel dibawah ini dan anggap saja tabel dibawah ini adalah tabel transaksi penjualan dan kita namai aja TBL_JUAL.

Pelanggan Barang Qty
------------------------------
Sembara      Pensil        12
XeroisNOcute Buku          45
SyamSyah     Handphone     12
Yudha        Buku           5
NOCuteangels Handphone      7
Bento        Handphone      9
ModBLog      Pensil         9
SyamSyah     Handphone      3
SyamSyah     Book          45

Dari tabel diatas si bos minta laporan dengan format seperti ini:

Pelanggan Buku Handphone Pensil
------------------------------------
Sembara      NULL      NULL      12
XeroisNOcute   45      NULL    NULL
SyamSyah       45        15    NULL
Yudha           5      NULL    NULL
NOCuteangels NULL         7    NULL
Bento        NULL         9    NULL
ModBlog      NULL      NULL       9

Nah… bikin crosstab query-nya gimana ???

Dalam Firebird ada salah satu fitur yang aku namai ‘SELECT dalam SELECT’ gak tau istilah resminya seperti apa untuk menggambarkan fitur tersebut :p Nested SELECT kali yaaa… Intinya melakukan GROUPING pada kolom Pelanggan dan melakukan penjumlahan pada kolom kedua (Buku) dengan kondisi barangnya harus Buku begitupun pada kolom ketiga (Handphone) dan keempat (Pensil). So.. crosstab query bisa seperti ini :

SELECT T1.PELANGGAN, (
SELECT SUM(T3.QTY)
FROM TBL_JUAL T3
WHERE T3.PELANGGAN = T1.PELANGGAN AND
T3.BARANG = 'Buku') BUKU, (
// Pelanggan pada T3 harus = pelanggan T1
SELECT SUM(T3.QTY)
FROM TBL_JUAL T3
WHERE T3.PELANGGAN = T1.PELANGGAN AND
T3.BARANG = 'Handphone') HANDPHONE, (
// Pelanggan pada T3 harus = pelanggan T1
SELECT SUM(T3.QTY)
FROM TBL_JUAL T3
WHERE T3.PELANGGAN = T1.PELANGGAN AND
T3.BARANG = 'Pensil') PENSIL
// Pelanggan pada T3 harus = pelanggan T1
FROM TBL_JUAL T1
GROUP BY T1.PELANGGAN

20 thoughts on “Membuat crosstab query (Posting Jadul)

  1. Kan sama aja mas… tinggal diganti nama kolomnya… dan laporan seperti ini sering diminta oleh pimpinan untuk mengetahui kondisi penjualan di lokasi-lokasi tertentu.

  2. Ndie_yach.. berkata:

    Hem…
    kalo untuk pengelompokan kenapa ga pake case when ajah…
    tuh kaya nya lebih enak.Dr pada harus melakukan proses perintah select trsz…😦

  3. mas kalau saya punya data :
    keterangan no faktur bulan
    Beli Beras 123456-A 2
    Beli Beras 54745-BC 3

    saya ingin jadikan crosstab tanpa sum
    jadi

    Keterangan 2 3
    No Faktur 123456-A 54745-BC

    begitu bagaimana apa bisa? soalnya saya coba tidak bisa, padahal ini tidak memerlukan sum karena yang dimunculkan string

  4. kalo di Mysql ada fungsi group_concat. Jadi sintaksnya begini:
    select ‘no_faktur’ as keterangan,
    group_concat(case when bulan=2 then no_faktur
    end) as ‘2’,
    group_concat(case when bulan=3 then no_faktur end)
    as ‘3’ from table1 group by ‘no_faktur’;

    Entah ya kalo di Firebird. Coba cari di versi terbaru mungkin ada yang mirip dengan fungsi tersebut.

  5. rama prakoso berkata:

    ouh iya mas gimana kalo laporan di delphi mau pakai crosstab tapi dia menggabungkan 2 tabel ,,sql nya gimana yaa mas ?? , di laporannyaa pakai adoquery , saya menggunakan sql 2000 mas,,tolong ya mas dibantu

  6. wida berkata:

    mas tolong aku dong aku pnya contoh laporan kayak gini

    nama customer Nama_barang jumlah
    hani phonska 10
    ana za 12
    ani Organik 15

    mau aku jadiin kayak gini

    Kode_cus phonska za organik
    hani 10 null Null
    ana null 12 null
    ani null null 15

    aku udah coba2 sintax crosstab kayak mas ajarin , udh aku coba di sql bisa tapi di adoquery laporan gak bisa yah ?? aku pake borland delphi , sintax aku ini mesti di tambahain atau di hapus apanya , , mohon bantuan nya mas ???

    SELECT T1.NAMA_CUSTOMER, (
    SELECT SUM(T3.JUMLAH)
    FROM TDETAIL_PEMESANAN T3
    WHERE T3.NAMA_BARANG = T1.NAMA_CUSTOMER AND
    T3.NAMA_BARANG = ‘PHONSKA’)PHONSKA, (
    SELECT SUM (T3.JUMLAH)
    FROM TDETAIL_PEMESANAN T3
    WHERE T3.NAMA_BARANG = T1.NAMA_CUSTOMER AND
    T3.NAMA_BARANG = ‘ORGANIK’)ORGANIK, (
    SELECT SUM (T3.JUMLAH)
    FROM TDETAIL_PEMESANAN T3
    WHERE T3.NAMA_BARANG = T1.NAMA_CUSTOMER AND
    T3.NAMA_BARANG = ‘ZA’)ZA
    SELECT SUM (T3.JUMLAH)
    FROM TDETAIL_PEMESANAN,TCUSTOMER T1
    GROUP BY T1.NAMA_CUSTOMER

  7. waduh mas, belom mudeng nih kyk nya, sya udah coba contoh diatas, tpi kalo di tambah 1 field lagi (date_create), jdi laporan data nya yg di tampilin ,, perbulan–

  8. data anggota

    status a = baru isi formulir
    status b = fiks_member
    status c = leave_member

    tabel nya kyk gini mas?

    user_input — tgl_input — nama_anggota — status a — status b — status c
    rizky 2013-05-02 wandi ok ok null
    deo 2013-03-04 ridwan ok cancel null
    dll

    jadi tampilan laporan nya?
    nampilin jumlah user masukin anggota perbulan nya,

    tampilan laporan nya?
    user status_a status_b status_c pada_bulan
    rizky 5 3 2 2013-05
    deo 9 4 1 2013-05

  9. tabel nya kyk gini mas?

    user_input—tgl_input—nama_anggota—status a—status b—status c
    rizky—–2013-05-02—–wandi—–ok—–ok—–null
    deo—–2013-03-04—–ridwan—–ok—–cancel—–null
    dll

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s