ORACLE-Transpose Column Value to Row in Oracle

You have a table with various columns. One of your column is a text column where the values are seperated with semicolon (";"). You want to transpose this column values into rows.

Generally we use instr function for this kind of purposes. But there is a another way to do this.

Let's suppose that your select statement is like this:

SELECT 'test;tester;siteconfidence;' TXT FROM DUAL
union all
SELECT 'AA;BB;TT;' TXT FROM DUAL

the output:

and our new statement:

WITH T AS(
SELECT 'test;tester;siteconfidence;' TXT FROM DUAL
union all
SELECT 'AA;BB;TT;' TXT FROM DUAL)
SELECT TXT FROM T
MODEL
RETURN UPDATED ROWS
PARTITION BY(ROWNUM RN)
DIMENSION BY (0 POSITION)
MEASURES (TXT,NVL(LENGTH(REGEXP_REPLACE(TXT,'[^;]+','')),0) NB_MOT)
RULES
(TXT[FOR POSITION FROM 1 TO NB_MOT[0] INCREMENT 1] = REGEXP_SUBSTR(TXT[0],'[^;]+',1,CV(POSITION)))

and the output:
 

Tweet