Converting Columns as row in Oracle

Today I was asked by someone saying that how we can convert the columns into rows having comma separated values.

For example let us take EMP table and ENAME is the column, so when we do a select on EMP table the expected result should be like:

OUTPUT:
ENAME
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,……

But in simple when we do a select on EMP table for ENAME column then the result looks like as below:

Code:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select ename from emp;
ENAME
———-
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.
SQL>

Here I am using Oracle 11g R2 database to show how we can achieve this.

So the expected result can be retrieved from the below queries:

Query by using XMLLAGG (introduced in 9i ):

SQL>SELECT rtrim (xmlagg (xmlelement (e, ename
|| ‘,’)).extract (‘//text()’), ‘,’) enames
FROM emp;

Output:
ENAMES
———-
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

Query by using LISTAGG :

SQL>SELECT listagg (ename, ‘,’) WITHIN GROUP ( ORDER BY ename) enames FROM emp;

Output:

ENAMES
———-
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

Can we get the same result by user defined functions? Yes we can ….

Below example shows how we can get the same result by creating our own function and selecting it:

# create function as GET_EMPLOYEES

CREATE OR REPLACE FUNCTION get_employees
RETURN VARCHAR2
IS
l_text VARCHAR2(32767) := NULL;
BEGIN
FOR cur_rec IN (SELECT ename FROM emp ) LOOP
l_text := l_text || ‘,’ || cur_rec.ename;
END LOOP;
RETURN LTRIM(l_text, ‘,’);
END;

Now let us write a select query to get the expected result as below:

SQL>select get_employees as enames from dual;

Output:

ENAMES
———-
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

Let us see how we can get the same result by using built-in functions:

SQL> SELECT wm_concat(ename) AS employees FROM emp;

In 10g we can make use of collect function, but this also works well in 11g as I am using oracle 11g R2 database:

Code:
CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab,
p_delimiter IN VARCHAR2 DEFAULT ‘,’) RETURN VARCHAR2 IS
l_string VARCHAR2(32767);
BEGIN
FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
IF i != p_varchar2_tab.FIRST THEN
l_string := l_string || p_delimiter;
END IF;
l_string := l_string || p_varchar2_tab(i);
END LOOP;
RETURN l_string;
END tab_to_string;
/

Once created the function run the below query to check the result:

SQL>SELECT tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
FROM emp ;

OUTPUT:

ENAMES
———-
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

But if we want the same result to be displayed as per department number then we can use a GROUP function to retrieve the expected result as like below:

SQL> SELECT deptno,
rtrim (xmlagg (xmlelement (e, ename
|| ‘,’)).extract (‘//text()’), ‘,’) enames
FROM
emp
group by deptno;

OR by using the below query:

SQL>select
deptno,
substr(SYS_CONNECT_BY_PATH(ename, ‘,’),2) name_list
from
(
select
ename,
deptno,
count(*) OVER ( partition by deptno ) cnt,
ROW_NUMBER () OVER ( partition by deptno order by ename) seq
from
emp
where
deptno is not null)
where
seq=cnt
start with
seq=1
connect by prior
seq+1=seq
and prior
deptno=deptno;

For both above mentioned query we will get the result like:

DEPTNO ENAMES
———————–
10 CLARK,MILLER,KING
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

Advertisements

3 thoughts on “Converting Columns as row in Oracle

  1. Superb experiment. One question and its been answered in so many ways. Truly amazing. Brilliant article for quick reference.

    Thanks

  2. Hi,
    Nice post.

    I have one question on this, If I have a data like below in one column,
    SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,
    FORD,MILLER……
    Now I want to convert it to,
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    ADAMS
    JAMES
    FORD
    MILLER

    how to do that?

    • You can do something like below :

      CREATE OR REPLACE TYPE SL_VARCHAR2_ARRAY AS TABLE OF VARCHAR2(4000);
      
      create or replace function split_string (i_string IN VARCHAR2, i_delimiter IN VARCHAR2)
            RETURN SL_VARCHAR2_ARRAY PIPELINED
         IS
            x_idx    PLS_INTEGER;
            x_list   VARCHAR2 (32767) := i_string;
         BEGIN
            LOOP
               x_idx := INSTR (x_list, i_delimiter);
      
               IF (x_idx > 0)
               THEN
                  PIPE ROW (SUBSTR (x_list, 1, x_idx - 1));
                  x_list := SUBSTR (x_list, x_idx + LENGTH (i_delimiter));
               ELSE
                  PIPE ROW (x_list);
                  EXIT;
               END IF;
            END LOOP;
      
            RETURN;
         END split_string;
         
      
      select * from table(split_string('a,b,c', ',')) ; 
      
      COLUMN_VALUE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      -----------
      a                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      b                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      c   
      

      Thanks,
      Sloba

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s