how to achieve dynamic columns adding in sql in below scenario in oracle sql

Question

question

sr_ca1 asked Oct 23, '20

how to achieve dynamic columns adding in sql in below scenario in oracle sql

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.

oracle

img-2.png (12.0 KiB)

img-3.png (9.2 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-11-02T02:14:49Z

Jeff Moden answered Nov 2, '20

Stop using the "ListAgg". It's not giving you want and will only require you to run things through a string splitter to get what you want. It's been nearly 20 years since I've written anything in Oracle but the following article should get you close.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

If you don't know the number of articles you want to do a CROSSTAB for, then see this followup article for how to do the things in the first article dynamically.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

Oracle may have some other magic functionality when it comes to doing such things automatically but, like I said, it's been 20 years since I last touched the keyboard on Oracle.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question