Dear all , Thank You for coming to my blogspot

Apr 22, 2014

cara merangkai data multi row dalam 1 kolom menggunakan ORACLE

Aku pernah dapat tugas merangkai data dalam beberapa row dari suatu kolom menjadi 1 set data kolom dalam sebuah row. Contohnya seperti ini

Account    Amount

  123            1000
  123            2000
  123            3000
  124            4000
  124            6000


Account    Amount

 123           1000,2000,3000
 124           4000,6000

untuk menyelesaikan challenge ini aku akan kasih 2 solusi, sbb :

solusi 1 :

select Account, rtrim(xmlagg (xmlelement (X, Amount || ',')).extract ('//text()') Amount
from namatable where rownum <800 Group By Account

solusi 2 :
select Account,wm_concat(Amount) as Amount from namatable where Amount <3000 Group By Account
Note :

Bila kita memanipulasi data begitu besar, maka akan mengakibatkan akumulasi panjang data yang begitu besar, oleh karena itu cara ini tentu ada keterbatasan, maka perlu membatasi panjang datanya dengan menggunakan rownum diatas.

Semoga bermanfaat

No comments :

About my own Company

Harsa Cipta Intiguna deliver a specific business domain in the IT World by providing IT Software Division so known as HarsaSoft, we would like to help and educate our client to gain the benefits from our highly skill, wide and deep knowledge, and fully experience experts in the related fields by getting the right sharped, effective and efficient software solution. We also create IT Hardware Division so called HarsaWare based on our market demand which could help you in gaining good quality gadgets, laptops and servers with competitive price and after sales warranty. We've made these possible to our existing client and we'll make these possible for you too by adapting and customizing to patch, enchance and adding rich features from the open source software, so you could use these features to help your business getting better and better a long the way as we would ourself getting better every day by making you satisfy with our products and services. We could provide end to end solution for you, so no need to worry that your IT System as the supporting business will become hard to manage and hard to improve for supporting the growing fast business. "No need reinventing the wheel but make it more useful" are the words that we have in mind and we also make sure that you leave your worries behind since we always give our client after-sales guaranty in order achieving our goal which is "Your satisfaction is ours". If you are interested to get the information about our products, please send an email to me, at

Note : please use your company email to request the brochure