Click here to Skip to main content
14,735,093 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a query which looks like

SELECT, n.odn_no, n.acc,
Listagg('Item ID:'|| (SELECT value FROM partner WHERE col1 = 'ABC' ) ,'|')
within GROUP( ORDER BY, n.odn_no, n.acc ) AS Item
FROM main_table n GROUP BY, 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
Updated 26-Oct-20 2:56am

1 solution

Use the pivot function.
There's a pretty good tutorial here: pivot and unpivot queries in 11g[^]

If your server is older than 11G you need to use CASE WHEN. Here's another tutorial: Example of Data Pivots in SQL (rows to columns and columns to rows) | Oracle FAQ[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900