Selamat malam agan" mohon bantuanya soal bagaima cara mempivot tabel dari hasil query ini:
Code:
SELECT vendor.nama_vendor, kriteria.nama_kriteria, kriteria.kode, nilai.nilai FROM nilai JOIN vendor ON nilai.id_vendor = vendor.id_vendor JOIN kriteria ON kriteria.id_kriteria = nilai.id_kriteria
Jadi hasil yang saya inginkan kurang lebih seperti ini, akan tetapi nilai dalam masing" kriteria tidak sama, saya salahnya dimana ya? mohon bantuanya..
Code:
SELECT vendor.nama_vendor, nilai.nilai = 1 as 'Bidang Usaha Vendor', nilai.nilai = 2 as 'Spesifikasi Barang / Jasa', nilai.nilai = 3 as 'Lama Pengiriman Barang / Jasa', nilai.nilai = 4 as 'Harga Barang', nilai.nilai = 5 as 'Diskon' FROM nilai JOIN vendor ON nilai.id_vendor = vendor.id_vendor JOIN kriteria ON kriteria.id_kriteria = nilai.id_kriteria GROUP BY vendor.nama_vendor
Dua sql ini akan menghasilkan pivot table yang sama
#1
PHP Code:
SELECT vendor.nama_vendor, n1.nilai AS 'Bidang Usaha Vendor', n2.nilai AS 'Spesifikasi Barang / Jasa', n3.nilai AS 'Lama Pengiriman Barang / Jasa', n4.nilai AS 'Harga Barang', n5.nilai AS 'Diskon' FROM nilai LEFT JOIN vendor ON vendor.id_vendor = nilai.id_vendor LEFT JOIN kriteria ON kriteria.id_kriteria = nilai.id_kriteria LEFT JOIN nilai n1 ON n1.id_kriteria = 1 AND n1.id_vendor = vendor.id_vendor LEFT JOIN nilai n2 ON n2.id_kriteria = 2 AND n2.id_vendor = vendor.id_vendor LEFT JOIN nilai n3 ON n3.id_kriteria = 3 AND n3.id_vendor = vendor.id_vendor LEFT JOIN nilai n4 ON n4.id_kriteria = 4 AND n4.id_vendor = vendor.id_vendor LEFT JOIN nilai n5 ON n5.id_kriteria = 5 AND n5.id_vendor = vendor.id_vendor GROUP BY vendor.nama_vendor
#2
PHP Code:
SELECT vendor.nama_vendor, (SELECT nilai.nilai FROM nilai WHERE nilai.id_vendor = vendor.id_vendor AND nilai.id_kriteria = 1 ) AS 'Bidang Usaha Vendor', (SELECT nilai.nilai FROM nilai WHERE nilai.id_vendor = vendor.id_vendor AND nilai.id_kriteria = 2 ) AS 'Spesifikasi Barang / Jasa', (SELECT nilai.nilai FROM nilai WHERE nilai.id_vendor = vendor.id_vendor AND nilai.id_kriteria = 3 ) AS 'Lama Pengiriman Barang / Jasa', (SELECT nilai.nilai FROM nilai WHERE nilai.id_vendor = vendor.id_vendor AND nilai.id_kriteria = 4 ) AS 'Harga Barang', (SELECT nilai.nilai FROM nilai WHERE nilai.id_vendor = vendor.id_vendor AND nilai.id_kriteria = 5 ) AS 'Diskon' FROM nilai LEFT JOIN vendor ON vendor.id_vendor = nilai.id_vendor LEFT JOIN kriteria ON kriteria.id_kriteria = nilai.id_kriteria GROUP BY vendor.nama_vendor
