甲骨文如何透视我的桌子 [英] oracle how to pivot my table

问题描述

我已将这些数据显示在图像中.

I have this data as displayed in images.

我想像这样显示这些数据

And I want to display this data like this

CYNAME    04 JAN 2012   03 JAN 2012 02 JAN 2012 01 JAN-2012
CUL            12           12             12         12

我如何在oracle 10g中实现此显示?

How can i achieve this in oracle 10g to display like that.

更新

我当前的Sql查询就是这样

My Current Sql Query is like this

SELECT CY_NAME,
           NVL(MAX(DECODE ( CA_DATE , (SELECT MAX(CA_DATE)   FROM COTTON_ARV), CA_VALUE )),0) "04 JAN 2013",
           NVL(MAX(DECODE ( CA_DATE , (SELECT MAX(CA_DATE)-1 FROM COTTON_ARV), CA_VALUE )),0) "03 JAN 2013",
           NVL(MAX(DECODE ( CA_DATE , (SELECT MAX(CA_DATE)-2 FROM COTTON_ARV), CA_VALUE )),0) "02 JAN 2013",
           NVL(MAX(DECODE ( CA_DATE , (SELECT MAX(CA_DATE)-3 FROM COTTON_ARV), CA_VALUE )),0) "01 JAN 2013",
           NVL(MAX(DECODE ( CA_DATE , (SELECT MAX(CA_DATE)-4 FROM COTTON_ARV), CA_VALUE )),0) "31 DEC 2012",
           NVL(MAX(DECODE ( CA_DATE , (SELECT MAX(CA_DATE)-5 FROM COTTON_ARV), CA_VALUE )),0) "30 DEC 2012",
           NVL(MAX(DECODE ( CA_DATE , (SELECT MAX(CA_DATE)-6 FROM COTTON_ARV), CA_VALUE )),0) "29 DEC 2012"
    FROM      V_COTTON_ARV
    GROUP BY  CY_NAME

问题是alis是固定的，查询中的日期是动态的.如何根据查询更改别名，或者有什么办法

Problem is that alis is fixed but date is dynamic inside the query.How can I change the alias according to query.Or is there any way to do this

推荐答案

在Oracle 11g之前，您不能使用简单的SQL语句来透视表.

Before Oracle 11g you can't pivot a table using a simple SQL statement.

您需要一个PL/SQL过程，该过程可以动态构建如下所示的枢轴查询:

You need a PL/SQL procedure that builds dinamically a pivoted query like this:

SELECT CYNAME    
     , MAX(DECODE(CA_DATE, TO_DATE('01-01-2013', 'DD-MM-YYYY'), CA_VALUE, NULL)) AS "01-01-2013"
     , MAX(DECODE(CA_DATE, TO_DATE('02-01-2013', 'DD-MM-YYYY'), CA_VALUE, NULL)) AS "02-01-2013"
     , MAX(DECODE(CA_DATE, TO_DATE('03-01-2013', 'DD-MM-YYYY'), CA_VALUE, NULL)) AS "03-01-2013"
     , ...
FROM MY_TABLE
GROUP BY  CYNAME;

或如果您只需要运行一次，则手动构建它.

or build it manually if you need to run it just once.

这篇关于甲骨文如何透视我的桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！