Sunday, May 24, 2015

How to convert rows into comma separated values in SQL Oracle (4 ways to convert)?







I have this requirement many times while coding interfaces in oracle apps. Every time, I use different options.  Here, I tried to list out the four different ways of achieving the same.
Let’s take the well-known SCOTT schema for our example. The requirement is to show the manager name along with sub-ordinate names in a comma separated list.
So first, let us simply join the tables to see the records,
clip_image001[4]

OPTION 1: LISTAGG function ( Tested in Oracle 11g)


SELECT   manager.ename manager,
         LISTAGG(suboridinates.ename,',') WITHIN GROUP (order by suboridinates.ename) suboridinates_list
    FROM emp suboridinates, emp manager
   WHERE manager.empno = suboridinates.mgr
   GROUP BY  manager.ename
ORDER BY manager.ename;
clip_image003[4]

OPTION 2: WM_CONCAT function (Tested in 11g version)

Caution: This function is undocumented and unsupported by Oracle

SELECT   manager.ename manager,
         wm_concat (suboridinates.ename) suboridinates_list
    FROM emp suboridinates, emp manager
   WHERE manager.empno = suboridinates.mgr
GROUP BY manager.ename
ORDER BY manager.ename;
clip_image004[4]

OPTION 3: XMLAGG and RTRIM Functions


SELECT   manager.ename manager,
         RTRIM (
          XMLAGG (XMLELEMENT (e, suboridinates.ename || ',')).EXTRACT ('//text()'),
          ',') suboridinates_list
    FROM emp suboridinates, emp manager
   WHERE manager.empno = suboridinates.mgr
   GROUP BY  manager.ename
ORDER BY manager.ename;
clip_image005[4]

OPTION 4: Always we can go for a custom stored function to satisfy our specific requirement.


CREATE OR REPLACE FUNCTION get_emp_list (pin_mgr IN NUMBER)
   RETURN VARCHAR2
IS
   lv_result_value   VARCHAR2 (2000);
BEGIN
   FOR i IN (SELECT ename
               FROM emp
              WHERE mgr = pin_mgr)
   LOOP
      lv_result_value := lv_result_value || ',' || i.ename;
   END LOOP;

   RETURN LTRIM (lv_result_value, ',');
END;
clip_image006[4]

Happy Sharing Smile Enjoy Learning Smile

0 Responses to “How to convert rows into comma separated values in SQL Oracle (4 ways to convert)?”

Post a Comment

Subscribe to: Post Comments (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search