Articles 
SQL Oracle SQL Oracle SQL - Convert given list (CSV) into Rows
SQL Oracle SQL Oracle SQL - Convert given list (CSV) into Rows
Tuesday, 30 August 2011 09:54
Situation
I have one column of values that I've suffixed a comma to but I want to display this as a rows in a table. This is for Oracle PL/SQL.
My List:
1, 2, 3, 4, 5, 6Yields:
1,2,3,4,5,6
Using this snippet
SELECT EXTRACT (VALUE (d), '//row/text()').getstringval () AS AppNo FROM (SELECT XMLTYPE ( 'Yields:' ) AS xmlval FROM DUAL) x, TABLE (XMLSEQUENCE (EXTRACT (x.xmlval, '/rows/row'))) d ' || REPLACE ( '1, 2, 3, 4, 5, 6' , ',', '
') || '
1 2 3 4 5 6
Note that I actually used text in the example above and this worked. Obviously don't get the commas mixed up.
| < Prev |
|---|
