Budi.P ...,
Untuk membuat sql model crosstab di MySql, bisa memakai model bertingkat. Contoh sqlnya:
SELECT t.ID_AS_URUT
,SUM(IF(t.x_grade = '6',1,0)) AS `g6`
,SUM(IF(t.x_grade = '5',1,0)) AS `g5`
,SUM(IF(t.x_grade = '4',1,0)) AS `g4`
,SUM(IF(t.x_grade = '3',1,0)) AS `g3`
,SUM(IF(t.x_grade = '2',1,0)) AS `g2`
,SUM(IF(t.x_grade = '1',1,0)) AS `g1`
,COUNT(*) AS Total
FROM (SELECT MAX(bu_1.GRADE) as x_grade,mid(bu_1.ID_SUBBID_BU,1, 2) AS x_bid,bu_1.NRBU,
bu_1.ID_AS_URUT FROM bu_1 where tg_cetak>='2012-1-1'
and tg_cetak<='2012-11-1' or selesai>='2012-1-1'
and selesai<='2012-11-1'
GROUP BY mid(bu_1.ID_SUBBID_BU,1, 2),bu_1.NRBU)
as t GROUP BY t.ID_AS_URUT WITH ROLLUP
Silahkan di otak-atik sesuai kebutuhan.
Semoga bisa membantu dan memberi semangat.
Hariyanto (Surabaya)
--------------------------------------------
On Wed, 5/3/14, Budi.P <bdpny@yahoo.com> wrote:
Subject: [belajar-access] buat Transform(crosstab) di mysql
To: belajar-access@yahoogroups.com
Date: Wednesday, 5 March, 2014, 1:33 PM
siang teman2,
sy
punya kendala buat query di mysql, mungkin ini sedikit
menyimpang dr topik,tapi
masih ada kaitannya krn fungsi di ms.accessnya menggunakan
crosstab,tapi
kendala di mysql.
sy
punya data sbb:
NAMA
TANGGAL
JUMLAH
TUKUL
01/03/2014
1
TUKUL
02/03/2014
2
TUKUL
03/03/2014
1
NAMA
01/03/2014
02/03/2014 03/03/2014
TUKUL
1
2
1
tapi tanggalnya selalu berubah-ubah sesuai
periode yang mau ditampilkan,trims buat teman2 yg
sudah mau bantu.
salamBudi
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar