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;

BULLK COLLECT and BULK UPDATE/INSERT with collections

1. Bulk collect using collection of records
   declare
   type rec_type IS RECORD(c1 number, c2 varchar2(10), c3 varchar2(20));
   type nest_type4 IS TABLE OF rec_type;
   var4 nest_type4;
   begin
   SELECT col1, col2, col3
   BULK COLLECT INTO var4
   FROM test1;
   end;
   /


2. Bulk collect using collection of objects (Use object type name in select statement)
   --Need to create object and types as SQL type by declaring them in the ---database instead of declaring them with in PL/SQL code.
   create or replace type rec_obj as object
   (c1 number, c2 varchar2(10), c3 varchar2(20));
   
   create or replace type nest_type3 IS TABLE OF rec_obj;
   
   declare
   var4 nest_type3;
   begin
   SELECT rec_obj(col1, col2, col3)
   BULK COLLECT INTO var4
   FROM test1;
   end;
   /


3. Bulk insert/update/delete in FORALL statements using collection of records
   declare
   type rec_type IS RECORD(c1 number, c2 varchar2(10), c3 varchar2(20));
   type rec_type1 IS RECORD(c2 varchar2(10), c1 number, c3 varchar2(20));
   type nest_type4 IS TABLE OF rec_type;
   type nest_type5 IS TABLE OF rec_type1;
   var4 nest_type4;
   var5 nest_type5;
   begin
   SELECT col1, col2, col3
   BULK COLLECT INTO var4
   FROM test1;

FORALL i IN var4.first..var4.last
INSERT INTO (SELECT col2, col1, col3 from test2)
values var4(i);

SELECT col2, col1, col3
BULK COLLECT INTO var5
FROM test1;

FORALL i IN var5.first..var5.last
UPDATE test2
SET ROW=var5(i);
end;
/

4. Bulk insert/update/delete in FORALL statements using collection of objects
--Need to create object and types as SQL type by declaring them in the ---database instead of declaring them with in PL/SQL code.
create or replace type rec_obj as object
(c1 number, c2 varchar2(10), c3 varchar2(20));

create or replace type nest_type3 IS TABLE OF rec_obj;

declare
var4 nest_type3;
begin
SELECT rec_obj(col1, col2, col3)
BULK COLLECT INTO var4
FROM test1;

FORALL i IN var4.first..var4.last
INSERT INTO test2 (col2, col1, col3)
values (TREAT(var4(i) as rec_obj).c1,
TREAT(var4(i) as rec_obj).c2,
TREAT(var4(i) as rec_obj).c3);

FORALL i IN var4.first..var4.last
UPDATE test2
SET col2=TREAT(var4(i) as rec_obj).c1,
col1=TREAT(var4(i) as rec_obj).c2,
col3=TREAT(var4(i) as rec_obj).c3;
end;
/

Issue with rownum on view

There is a basic view which has complex join on some big tables. Since there are necessary indices for the query the query cost is small and runs very fast as below

CREATE OR REPLACE VIEW myview
SELECT col1,
Col2, …..
FROM bigtable1, bigtable2,…
WHERE bigtable1.col1=bigtable2.col1…..

SELECT *
FROM myview;

Runs fast in less than a second. Once the query includes a where clause constraint on rownum as below.

SELECT *
FROM myview
WHERE rownum<:n;

The query runs for ever and its cost increases tremendously. The plan shows full table scans on these table. It seems like optimizer assumes that since it needs to fetch :n number of rows why not do a full table scan.

The trick I found which fixed this query is using an order by clause as suggested by tom at this link http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

By using
CREATE OR REPLACE VIEW myview
SELECT col1,
Col2, …..
FROM bigtable1, bigtable2,…
WHERE bigtable1.col1=bigtable2.col1…..
Order by col1;

And


SELECT *
FROM myview
WHERE rownum<:n;

Fixed the problem. By using order by with rownum on outer query optimizer does sort on only first n number of rows. Once the rownum > n satisfies it returns the result set. That way optimizer keeps the plan intact as estimated and avoids full table scan.