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
id odn_no acc Item
1 odn1 ac1 p1|p2
2 odn2 ac2 p2|p3|p4
3 odn4 ac3 p6|p7|p8|p9|p5|p13|p14 and so on which are dynamic from the query results
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
id odn_no acc Item1 Item2 Item3 Item4 Item5 Item6 Item7
1 odn1 ac1 p1 p2
2 odn2 ac2 p2 p3 p4
3 odn4 ac3 p6 p7 p8 p9 p5 p13 p14
Pls help me how to this in Oracle Sql/plsql. Thanks in advance.
What I have tried:
I have tried doing with a temp table by calculating the no. of max columns that a row can produce and depending on that create a temp table but not getting exactly how to create and insert the data