Click here to Skip to main content
15,867,453 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.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
Posted
Updated 26-Oct-20 1: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[^]
 
Share this answer
 

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