Skip to content

Instantly share code, notes, and snippets.

@ebta
Created February 2, 2013 03:15
Show Gist options
  • Save ebta/4695992 to your computer and use it in GitHub Desktop.
Save ebta/4695992 to your computer and use it in GitHub Desktop.
Easy way to create crosstab query (monthly based aggregate) in Oracle, without using function or procedure (which is faster)
SELECT
field_1,
id_master,
sum(decode(bln,'01',jumlah)) jan,
sum(decode(bln,'02',jumlah)) feb,
sum(decode(bln,'03',jumlah)) mar,
sum(decode(bln,'04',jumlah)) apr,
sum(decode(bln,'05',jumlah)) mei,
sum(decode(bln,'06',jumlah)) jun,
sum(decode(bln,'07',jumlah)) jul,
sum(decode(bln,'08',jumlah)) ags,
sum(decode(bln,'09',jumlah)) sep,
sum(decode(bln,'10',jumlah)) okt,
sum(decode(bln,'11',jumlah)) nov,
sum(decode(bln,'12',jumlah)) des
FROM (
SELECT
m.field_1,
d.id_master,
TO_CHAR(d.field_date,'MM') bln,
SUM(d.jumlah) jumlah
FROM
detail d, master m
WHERE
d.id_master = m.id_master
GROUP BY
m.field_1,d.id_master,TO_CHAR(d.field_date,'MM')
) ab
GROUP BY
field_1,id_master
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment