Oracle Database | rohmad.net

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;