Pivot Query: konversi row ke column

Inti pivot query adalah menampilkan data row menjadi column. Contoh praktisnya, saya punya data berikut ini

create table trx(PART varchar2(1), TRX_DATE date,
DOC_NO varchar2(10), TRX_CODE varchar2(1), AWAL number,
TRX_QTY number,AKHIR number);


insert into trx values('A',to_date('01-01-08','dd-mm-yy'),
'DOC 1','I',10, 5 , 15);
insert into trx values('A',to_date('01-01-08','dd-mm-yy'),
'DOC 2','I',15 , 5 , 20);
insert into trx values('A',to_date('01-01-08','dd-mm-yy'),
'DOC 3','O',20, 10 , 10 );
insert into trx values('B',to_date('01-01-08','dd-mm-yy'),
'DOC 1','I',100 , 25 , 125 );
insert into trx values('B',to_date('01-01-08','dd-mm-yy'),
'DOC 2','I', 125 , 25 , 150 );
insert into trx values('B',to_date('01-01-08','dd-mm-yy'),
'DOC 3','O', 150 , 75 , 75 );
insert into trx values('A',to_date('02-01-08','dd-mm-yy'),
'DOC 4','O',10 , 5 , 5);
insert into trx values('A',to_date('02-01-08','dd-mm-yy'),
'DOC 5','I', 5 , 5 , 10);
insert into trx values('A',to_date('02-01-08','dd-mm-yy'),
'DOC 6','O',10 , 10 , 0);
insert into trx values('B',to_date('02-01-08','dd-mm-yy'),
'DOC 4','I',75 , 25 , 100 );
insert into trx values('B',to_date('02-01-08','dd-mm-yy'),
'DOC 5','I',100 , 25 , 125);
insert into trx values('B',to_date('02-01-08','dd-mm-yy'),
'DOC 6','O',125 , 100 , 25 );

SQL> select * from trx order by TRX_DATE,part;

Lihat kolom TRX_CODE dan TRX_QTY. Nilai kolom TRX_CODE adalah I dan O. Saya ingin menampilkan total transaksi (TRX_QTY) untuk TRX_IN (di mana TRX_CODE = I) dan TRX_OUT (di mana TRX_CODE = O)


TRX_IN TRX_OUT
------ ------
   115    200

Untuk menampilkan hasil query di atas, perintah SQL nya adalah sebagai berikut
SQL> SELECT
MAX(DECODE(TRX_CODE,'I',total,null)) TRX_IN,
MAX(DECODE(TRX_CODE,'O',total,null)) TRX_OUT
FROM
(
SELECT TRX_CODE,sum(TRX_QTY) total
FROM trx
GROUP BY TRX_CODE
)
;

Sekarang, saya ingin menampilkan total transaksi (TRX_QTY) untuk TRX_IN dan TRX_OUT masing-masing PART tiap harinya:

Command SQL untuk hasil query di atas adalah
SELECT PART,TRX_DATE,
MAX(DECODE(TRX_CODE,'I',total,null)) TRX_IN,
MAX(DECODE(TRX_CODE,'O',total,null)) TRX_OUT
FROM
(
SELECT PART,trunc(TRX_DATE) TRX_DATE,TRX_CODE,sum(TRX_QTY) total
FROM trx
GROUP BY TRX_CODE, PART,trunc(TRX_DATE)
)
group by PART,TRX_DATE
order by TRX_DATE,PART
;

Bisa juga gunakan perintah SQL berikut ini:
SELECT DISTINCT part, date_trx, trx_in, trx_out FROM (
SELECT part, trx_date date_trx,
SUM(DECODE(trx_code,'I',trx_qty,0)) OVER (PARTITION BY part, trx_date)
trx_in,
SUM(DECODE(trx_code,'O',trx_qty,0)) OVER (PARTITION BY part, trx_date)
trx_out
FROM trx)
ORDER BY date_trx, part;

Sekarang saya ingin menambahkan tampilan data-data berikut ini
– AWAL : Diambil dari nilai awal dari transaksi pertama part tsb.
– AKHIR : Diambil dari nilai akhir dari transaksi terakhir part tsb.

Perintah SQL untuk hasil query di atas adalah (hint: gunakan analytic function) :
SELECT DISTINCT part, date_trx, awal, trx_in, trx_out, akhir FROM (
SELECT part, trx_date date_trx,
FIRST_VALUE(awal) OVER (PARTITION BY part, trx_date ORDER BY doc_no) awal,
SUM(DECODE(trx_code,'I',trx_qty,0)) OVER (PARTITION BY part, trx_date)
trx_in,
SUM(DECODE(trx_code,'O',trx_qty,0)) OVER (PARTITION BY part, trx_date)
trx_out,
LAST_VALUE(akhir) OVER (PARTITION BY part, trx_date) akhir
FROM trx)
ORDER BY date_trx, part;

This entry was posted in SQL and PL/SQL and tagged , , , , . Bookmark the permalink.

5 Responses to Pivot Query: konversi row ke column

  1. Benny Kusno B says:

    Pak, mohon bantuannya contoh spt di atas untuk field dated_invoiced jumlah row nya yg ada atas 10 jt row dan range dated_invoiced mulai thn 2004 s/d saat ini trus kita query berdasar dari where condition yang di input melalui terminal dari tgl s/d tgl terserah user dated_invoiced YYYYMM di convert to_char(dated_invoiced,’YYYYMM’ sebagai periode perthnbln ( 200501,200502,200503 ) hasil yg keluar adalah code_product,code_description,200501,200502.200503 … sesuai where condition di input tentunya sama bilamana column nya bisa per 3 bln ( 20050103,20050406,… )

  2. rohmadne says:

    Hallo…

    Ini mirip dengan yang saya bahas di sini
    http://tech.groups.yahoo.com/group/indo-oracle/message/17782

  3. edfin says:

    Dear Pak,mohon bantuannya untuk weekly reporting.
    row data harian,dan untuk week define sendiri(misal w1=2009-01-05 s/d 2009-01-11

  4. Djoe says:

    Yth pak Rohmad

    Minta saran pembuatan pivot kolom yg dinamis, misal
    nama , umur_1, umur_2, umur_n
    xx 1 2 n

    Terima kasih

  5. falconer says:

    mas rohmad mau tanya donk aku punya query seperti ini :

    SELECT DISTINCT A.TANGGAL, “DOC 1”, “DOC 2”, “DOC 4” FROM
    (SELECT TRX_DATE TANGGAL,DOC_NO, SUM(CASE DOC_NO WHEN ‘DOC 1’ THEN TRX_QTY ELSE NULL END) AS “DOC 1” FROM TRX
    WHERE TRX_CODE = ‘I’
    GROUP BY TRX_DATE,DOC_NO, DOC_NO)A,

    (SELECT TRX_DATE TANGGAL,DOC_NO, SUM(CASE DOC_NO WHEN ‘DOC 2’ THEN TRX_QTY ELSE NULL END) AS “DOC 2” FROM TRX
    WHERE TRX_CODE = ‘I’
    GROUP BY TRX_DATE,DOC_NO, DOC_NO)B,

    (SELECT TRX_DATE TANGGAL,DOC_NO, SUM(CASE DOC_NO WHEN ‘DOC 4’ THEN TRX_QTY ELSE NULL END) AS “DOC 4” FROM TRX
    WHERE TRX_CODE = ‘I’
    GROUP BY TRX_DATE,DOC_NO, DOC_NO)C

    kenapa kok punculnya seperti ini ya :

    TANGGAL DOC 1 DOC 2 DOC 4

    1/2/2008
    1/1/2008 30 30
    1/2/2008 25
    1/1/2008 30 25
    1/1/2008 30
    1/1/2008 30
    1/2/2008 30 25
    1/2/2008 30
    1/1/2008 30 30 25
    1/1/2008
    1/1/2008 25
    1/1/2008 30 25

    padahal yang aku inginkan datanya cuman seperti ini :
    TANGGAL DOC 1 DOC 2 DOC 4
    1/1/2008 30 30
    1/2/2008 25

    bagaimana query yang sebenernya dari kasus saya tersebut.. terima kasih…

Leave a Reply

Your email address will not be published. Required fields are marked *