Retrieving result dynamically from pivot or pivot XML in oracle

See more:

SQL

Oracle

Hi,

I want to get the data using Pivot dynamically but not able to get if possible then please let me know i'm sending my query here below

SELECT * FROM(
                SELECT ROW_NUMBER() OVER (PARTITION BY IAI_COVERNUMBER,IAI_VERNO ORDER BY IAI_COVERNUMBER) AS RNO, IAI_COVERNUMBER AS INSGRP 
                FROM IAI_ACTUALINSURERS INNER JOIN PLY_POLICY ON  (IAI_COVERNUMBER=PLY_COVERNUMBER AND IAI_VERNO=PLY_VERNO)
                WHERE IAI_COVERNUMBER in ('0011520','0010891') 
                
            ) PIVOT (MIN(INSGRP)--,MIN(IAI_INSPROPORTION) AS INSPROP              
              FOR RNO IN (1,2,3,4,5));

here i've set RNO hard-coded but i want it the number of rows exist

SELECT ROW_NUMBER() OVER (PARTITION BY IAI_COVERNUMBER,IAI_VERNO ORDER BY IAI_COVERNUMBER) AS RNO

But i'm also using Pivot XML but it is giving me output in XML sting in a single column
<PivotSet>>item><column name RNO= >1</column><column name = INSGRP>BAGIC-B02-100-OG08190133030000052</column></item><item><column name = RNO>2</column><column name = INSGRP></column></item></PivotSet></i></b>

but i want INSGRP values in no. of columns as above that will give us 2 columns ....it may be more than 2 columns


Please provide the solution if anybody has face or know solution.

What I have tried:

above i mentioned everything what i want and have tried