I have a query which looks like
SELECT n.id, n.odn_no, n.acc,
Listagg('Item ID:'|| (SELECT value FROM partner WHERE col1 = 'ABC' ) ,'|')
within GROUP( ORDER BY n.id, n.odn_no, n.acc ) AS Item
FROM main_table n GROUP BY n.id, n.odn_no, n.acc
produces results like below
Now i would need to change the last column Item which was a listagg value earlier.
now I have to split the column Item to Item1 Item2 Item3 and so on depending on the results that is produced by the Listagg. For suppose if a row has 10 values seperated by the delimiter '|' for the column Item, then the Headers of the result should have Item1, Item2 , Item3 ... Item10.
If a record has only 2 values for this field then it should come under Item1, Item2 and remaining will be empty. i.e., for the above results, I should show like below
Pls help me how to do this in Oracle Sql/plsql. Thanks in advance.