在oracle中从pivot或pivot XML动态检索结果 [英] Retrieving result dynamically from pivot or pivot XML in oracle

问题描述

我想动态地使用 Pivot 获取数据，但如果可能则无法获取数据然后请让我知道我我在下面发送我的查询

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));

这里我设置了 RNO 硬编码，但我想要它存在的行数

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

但是我也在使用 Pivot XML 但是它在 XML 中输出了一列中的输出

< PivotSet>> item><列名RNO => 1< / column><列名= INSGRP> BAGIC-B02-100-OG08190133030000052< / column>< / item>< item>< column name = RNO> 2< / column>< column name = INSGRP>< / column>< / item>< / PivotSet>< / i>< / b>



但我希望 INSGRP 的值为no。上面的列将给我们2列......它可能超过2列





请提供解决方案任何人都有面子或知道解决方案。



我尝试过：



上面我提到了我想要的所有东西并尝试了

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

推荐答案

不确定我是否完全理解你的问题，但如果你的意思是你想要有动态数量的列PIVOT子句然后你需要动态地构建SQL语句。



换句话说，根据具体情况你需要

Not sure if I fully understand your question but if you mean that you want to have dynamic amount of columns in PIVOT clause then you need to build the SQL statement dynamically.

In other words depending on the situation if you need to have

PIVOT (MIN(INSGRP) FOR RNO IN (1,2,3,4,5));

或

or

PIVOT (MIN(INSGRP) FOR RNO IN (1,2,3,4,5,6,7,8,9));

或

or

PIVOT (MIN(INSGRP) FOR RNO IN (1,2,3));

你需要由于数据透视不支持动态值，因此不同的SQL语句。请参阅询问Tom是否可以使用动态查询PIVOT in cl ... [ ^ ]

但是如果使用Pivot XML，则可以使用子查询作为列。有关示例，请查看 oracle sql中的动态数据库 - Stack Overflow [ ^ ]

这篇关于在oracle中从pivot或pivot XML动态检索结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！