TRANSPOSE CSV column to ROWS

Question:
How can we transfer a csv (comma separated value) column into rows?

ex:

SQL> desc test1

Name Type
COL1 NUMBER
COL2 VARCHAR2(20)

SQL> select * from test1;

COL1 COL2
-------------------------

1 Aaa,BC,DEF,Gjnjk

Col2 should be converted to rows like
Aaa

BC

DEF

Gjnjk


Answer:
The key here is to use LEVEL clause of heirarchal query to make query go through number of commas separated strings in the column and use substring functions to strip approriate substring at each level. This query also display starting position and number of characters used at each level. This is shown just convenience and document purposes only. For actual conversion you may not need cols like st_pos and num_of_char.

SELECT col2,
/*Starting position. For level 1 it will be 1. After that for each levels it is LEVEL-1'th position of ","*/
DECODE(LEVEL,1,1,INSTR(col2,',',1,LEVEL-1)+1) as st_pos,
/*Number of characters. It is substraction of LEVEL-1'th position of ",' from LEVEL'th position of ",". For LEVEL 1 this will be 0*/

DECODE( DECODE(LEVEL, 1, INSTR(col2,',',1,LEVEL)-1, INSTR(col2,',',1,LEVEL)),
0, LENGTH(col2)+1, --if above 0 then string length + 1
DECODE(LEVEL,1, INSTR(col2,',',1,LEVEL)-1, INSTR(col2,',',1,LEVEL))) - --else LEVEL'th position
DECODE(LEVEL,1,0, INSTR(col2,',',1,LEVEL-1)+1) as num_of_char,
SUBSTR(col2,

DECODE(LEVEL,1,1,INSTR(col2,',',1,LEVEL-1)+1),--startingposition

DECODE( DECODE( LEVEL, 1, INSTR(col2,',',1,LEVEL)-1, INSTR(col2,',',1,LEVEL)) ,
0, LENGTH(col2)+1,
DECODE(LEVEL, 1, INSTR(col2,',',1,LEVEL)-1, INSTR(col2,',',1,LEVEL))) -
DECODE(LEVEL, 1, 0, INSTR(col2,',',1,LEVEL-1)+1))) --num of characters

FROM (select col2
FROM test1
WHERE col1=1
) vw
CONNECT BY LEVEL <= (LENGTH(vw.col2)-LENGTH(REPLACE(vw.col2,',')))+1;

COL2 ST_POS NUM_OF_CHAR SUBSTR(COL2,DECODE(L
---------------------------------------------------------------------------------------------
Aaa,BC,DEF,Gjnjk 1 3 Aaa
Aaa,BC,DEF,Gjnjk 5 2 BC
Aaa,BC,DEF,Gjnjk 8 3 DEF
Aaa,BC,DEF,Gjnjk 12 5 Gjnjk

But, wouldn't it be convenient if we had a function to which we can pass the column that we need to parse as rows and use them in some query. Well of course there is a solution for that too. Just use the above query in a function and return a database array type, so it can be used as subquery in any query.

In our example here we have line item details in line_order table as order no in ord_no and its contact information un ord_contact, which is csv column of contact's first names.

There is another table cust_info, which has customer first name, last name and their email address.

SQL> desc line_order
Name Type
ORD_NO NUMBER
ORD_CONTACT VARCHAR2(100)

SQL> ;
1* select * from line_order;

ORD_NO ORD_CONTACT
---------- --------------------------------
1 John, Mary, Michael, Michele

SQL> desc cust_info
Name Type
---------------------
FNAME VARCHAR2(20)
LNAME VARCHAR2(20)
MAIL VARCHAR2(500)

SQL> select * from cust_info;

FNAME LNAME EMAIL
------------------------------------
John Doe John@email.com
Mary Ann Mary@email.com
Michael Angelo Michael@email.com
Michele Wang Michele@email.com

--Database type, which is a nested table of varchar2
CREATE OR REPLACE TYPE STRING_TYPE_ARRAY AS TABLE OF VARCHAR2(32767);

/***********************************************************************************
Function to convert a csv column into rows. This function takes the query string as input,
converts csv column from the query into rows and return a database defined type which is
nested table of varchar2.
************************************************************************************/


CREATE OR REPLACE FUNCTION csv_to_rows(p_csv_col_qry VARCHAR2)
RETURN string_type_array
AS
l_string_type_array string_type_array;
l_csv_col VARCHAR2(32767);
BEGIN
EXECUTE IMMEDIATE p_csv_col_qry
INTO l_csv_col;

SELECT vw2.new_col
BULK COLLECT INTO l_string_type_array
FROM (SELECT col2,
LEVEL,
DECODE(LEVEL,1,1,INSTR(col2,',',1,LEVEL-1)+1) as st_pos, -- Starting position. For level 1 it will be 1. After that for each levels it LEVEL-1'th position of ","
DECODE(DECODE(LEVEL,1,INSTR(col2,',',1,LEVEL)-1,INSTR(col2,',',1,LEVEL)),0,LENGTH(col2)+1,DECODE(LEVEL,1,INSTR(col2,',',1,LEVEL)-1,INSTR(col2,',',1,LEVEL))) -
DECODE(LEVEL,1,0,INSTR(col2,',',1,LEVEL-1)+1) as num_of_char, -- Number of character. It is substraction of LEVEL-1'th position of ",' from LEVEL'th position of ",". For LEVEL this will be 0
SUBSTR(col2,
DECODE(LEVEL,1,1,INSTR(col2,',',1,LEVEL-1)+1),
DECODE(DECODE(LEVEL,1,INSTR(col2,',',1,LEVEL)-1,INSTR(col2,',',1,LEVEL)),0,LENGTH(col2)+1,DECODE(LEVEL,1,INSTR(col2,',',1,LEVEL)-1,INSTR(col2,',',1,LEVEL))) -
DECODE(LEVEL,1,0,INSTR(col2,',',1,LEVEL-1)+1)) new_col
FROM (SELECT l_csv_col as col2
FROM dual
) vw
CONNECT BY LEVEL <= (LENGTH(vw.col2)-LENGTH(REPLACE(vw.col2,',')))+1
) vw2;

RETURN l_string_type_array;
END csv_to_rows;


This function can be used as below

SELECT ci.fname,
ci.email
FROM cust_info ci
INNER JOIN (SELECT COLUMN_VALUE as fname
FROM TABLE( CAST(csv_to_rows('SELECT lo.ord_contact FROM line_order lo WHERE ord_no=1') as string_type_array))
) vw
ON trim(vw.fname)=trim(ci.fname);

FNAME EMAIL
-----------------------
John John@email.com
Mary Mary@email.com
Michael Michael@email.com
Michele Michele@email.com

TRANSPOSE ROWS TO COLUMNS AND CONVERT TO COMMA SEPRATED VALUES (CSV)

/*Below is the basic sql to transpose rows to columns and concatenate by ','
The key here is using hierarchical queries and oracle supplied sys_connect_by_path function
*/
WITH s1 AS
( SELECT 'A' as col1
FROM dual
UNION
SELECT 'B' as col1
FROM dual
UNION
SELECT 'C' as col1
FROM dual)
SELECT SUBSTR(MAX(sys_connect_by_path(col1,',')),2)
FROM (SELECT col1,
row_number () OVER (ORDER BY col1) as curr,
row_number () OVER (ORDER BY col1)-1 as prev
FROM s1
) vw
CONNECT BY vw.curr = PRIOR prev
SQL> /
SUBSTR(MAX(SYS_CONNECT_BY_PATH(COL1,',')),2)
--------------------------------------------------------------------------------
C,B,A


Here set s1 can be replace with any query data that you want to convert to csv.

To use this as a function/procedure pass the cursor and use its result set as set s1 in the query above. You would need to use a character database type array to collect cursor data

for example:
CREATE OR REPLACE TYPE string_type_array AS TABLE OF VARCHAR2(32767);


CREATE OR REPLACE FUNCTION test_func(p_cur IN OUT sys_refcursor)
RETURN VARCHAR2
AS
l_col_val_tab string_type_array;
l_csv_string VARCHAR2(32767);
BEGIN
LOOP
FETCH p_cur BULK COLLECT INTO l_col_val_tab;
EXIT WHEN p_cur%NOTFOUND;
END LOOP;

WITH s1 AS
(SELECT COLUMN_VALUE as col1
FROM TABLE(l_col_val_tab)
)
SELECT SUBSTR(MAX(sys_connect_by_path(col1,',')),2)
INTO l_csv_string
FROM (SELECT col1,
row_number () OVER (ORDER BY col1) as curr,
row_number () OVER (ORDER BY col1)-1 as prev
FROM s1
) vw
CONNECT BY vw.curr = PRIOR prev;

RETURN l_csv_string;
END test_func;


DECLARE
l_query_cur SYS_REFCURSOR;
BEGIN
OPEN l_query_cur FOR
SELECT 'A' as col1
FROM dual
UNION
SELECT 'B' as col1
FROM dual
UNION
SELECT 'C' as col1
FROM dual;

DBMS_OUTPUT.put_line(test_func(p_cur => l_query_cur));
END;