Retrieving result dynamically from pivot or pivot XML in ora

Question

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.