Thanks Andre, Mamed, dan Achmad Maulana...akan saya coba explore. Regards
IH --- On Mon, 4/13/09, achmad maulana <[email protected]> wrote: From: achmad maulana <[email protected]> Subject: Bls: [indo-oracle] help : query matrix To: [email protected] Date: Monday, April 13, 2009, 1:58 PM Dear Hanafie, Berikut sy berikan hasil ulik2an sy tentang data matrix 3 column malam ini, menurut sy solusiny untuk data matrix bs banyak cara spt pake table array, atau pake execute immediate create view, tapi mgk solusi yang dibawah ini bagi sy yang paling simple, mudah2an dapat bermanfaat dan bs dikembangkan sesuai dengan kebutuhan. Thx Regards, Amaulana declare hdr varchar2(1000); printname varchar2(1000); vmsk number; pjg number; begin hdr := '| Description'; for i in (select company_tujuan from dev_data_matrix_lanjutan2 group by company_tujuan order by company_tujuan) loop hdr := hdr ||' | '||i.company_tujuan; end loop; select length(hdr) into pjg from dual; dbms_output.put_line(hdr||' | '); dbms_output.put_line(rpad('-',pjg,'-')); for r1 in (select company_asal from dev_data_matrix_lanjutan2 group by company_asal order by company_asal) loop for r2 in (select company_tujuan from dev_data_matrix_lanjutan2 group by company_tujuan order by company_tujuan) loop vmsk := 0; for r3 in (select * from dev_data_matrix_lanjutan2 where company_asal = r1.company_asal and company_tujuan = r2.company_tujuan) loop vmsk := 1; printname := printname ||' | '||r3.nilai; end loop; if vmsk = 0 then printname := printname ||' | 0'; end if; end loop; dbms_output.put_line('| '||r1.company_asal||printname||' | '); printname := null; end loop; dbms_output.put_line(rpad('-',pjg,'-')); end; Outputnya kira2 spt ini : | Description | A | B | C | ------------------------- | A | 0 | 1000 | 100 | | B | 1000 | 0 | 0 | | C | 100 | 0 | 0 | ------------------------- Dari: Izzudin Hanafie <[email protected]> Kepada: [email protected] Terkirim: Senin, 13 April, 2009 16:12:45 Topik: Fw: [indo-oracle] help : query matrix Wah...kok tampilannya malah jadi aneh yaa..padahal sudah dibuat table2nya. Berikut saya rapihkan kembali sumber data dan hasil yang diinginkan : Sumber data : ------------ --------- --------- --------- --------- --------- - Company Asal Company Tujuan Nilai ------------ --------- --------- --------- --------- --------- - Company A Company B 1000 Company A Company C 100 Company B Company A 1000 Company B Company C 0 Company C Company A 100 Company C Company B 0 ------------ --------- --------- --------- --------- --------- - Hasil yang diinginkan : ------------ --------- --------- --------- --------- --------- - Description Company A Company B Company C ------------ --------- --------- --------- --------- --------- - Company A 0 1000 100 Company B 1000 0 0 Company C 100 0 0 ------------ --------- --------- --------- --------- --------- - Regards IH --- On Mon, 4/13/09, Izzudin Hanafie <izzudin...@yahoo. com> wrote: From: Izzudin Hanafie <izzudin...@yahoo. com> Subject: [indo-oracle] help : query matrix To: indo-oracle@ yahoogroups. com Date: Monday, April 13, 2009, 4:58 AM Dear Guru’s, Saya mempunyai problem membuat query matrix dari data berikut : Company Asal Company Tujuan Nilai Company A Company B 1000 Company A Company C 100 Company B Company A 1000 Company B Company C 0 Company C Company A 100 Company C Company B 0 Sumber data Saat ini untuk mengeluarkan data tersebut dilakukan dengan melakukan grouping per company lalu dilakukan join, masalahnya yaitu terdapat 46 entitas company untuk membuat tampilan matrix seperti dibawah. Sepertinya terlalu kompleks bila dibuat grouping per company sebanyak 46 buah. Description Company A Company B Company C Company A 0 1000 100 Company B 1000 0 0 Company C 100 0 0 Hasil yang diharapkan Apakah ada cara lain untuk membuat query report tersebut yang lebih simple ?.. Mohon bantuannya, Regards, Izzudin hanafie [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] Dapatkan nama yang Anda sukai! Sekarang Anda dapat memiliki email di @ymail.com dan @rocketmail.com. [Non-text portions of this message have been removed]
