oracle中的动态数据透视（在子句中） [英] Dynamic pivot in oracle( in clause)

问题描述

我有这样的数据

I have a data like this

TYPE  RANGE               SALES
A     20180301-20180101     100
A     20171201-20171031   150 
A     20170930-20170731    100
B     20180301-20180101    200
B     20171201- 20171031   200

i想要这样的输出

i want output like this

TYPE 20180301-20180101   20171201-20171031 20170930-20170731
A      100                   150               100

我的尝试：



这就是我的尝试

What I have tried:

This is what I have tried

SELECT
FROM
(
SELECT TYPE,MONTH_RANGE,SALES
FROM TABLE
)
pivot
(
    SUM(SALES)
    FOR (MONTH_RANGE) IN (SELECT DISTINCT MONTH_RANGE FROM TABLE)
)

我也尝试使用XML，但它的格式不正确

And I tried with XML as well, but its not in a proper format

pivot xml (SUM(SALES) for (month_range) in (SELECT DISTINCT MONTH_RANGE FROM TABLE))

我希望这个月的范围是动态的，不像

I want this month range to be dynamic not like

for month_range in ('20180301-20180101')

推荐答案

据我所知，你无法实现动态旋转。执行SQL语句时，系统需要在编译时知道结果集的结构。如果列表列表是动态的，则无法实现这一点



因此，有一种可能性是分两个阶段执行此操作，首先获取范围，然后构建SQL语句，执行它。另一种方法可能是忘记dynamin pivot并使用XMLELEMENT获取动态部分

As far as I know you cannot achieve dynamic pivoting. When a SQL statement is executed, the system needs to know the structure of the result set at compile time. This cannot be achieved if the list of columns would by dynamic

So one possibility is to do this in two phases, first fetch the ranges and then build the SQL statement and execute it. Another approach could be to forget the dynamin pivot and fetch the dynamic portion using a XMLELEMENT

我建​​议阅读： oracle sql中的动态数据透视 - Stack Overflow [ ^ ] - 第二个解决方案。

I'd suggest to read this: Dynamic pivot in oracle sql - Stack Overflow[^] - second solution.

这篇关于oracle中的动态数据透视（在子句中）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！