Oracle SQL：在多个列字段上进行透视

概述我想创建一个表,其中各列的条目“旋转”到列标题.该表用于报告目的 – 我的用户希望通过Excel(使用Microsoft Query)查询数据,问题是在Excel中进行数据透视会使文件变得非常大且速度慢,即使对于中等大小的数据集(~100k数据点)也是如此). 请考虑以下示例： CREATE TABLE tt( "COMMODITY" VARCHAR2(4000 BYTE), " 我想创建一个表,其中各列的条目“旋转”到列标题.该表用于报告目的 – 我的用户希望通过Excel(使用Microsoft query)查询数据,问题是在Excel中进行数据透视会使文件变得非常大且速度慢,即使对于中等大小的数据集(~100k数据点)也是如此).

请考虑以下示例：

CREATE table tt(     "commodity" VARCHAR2(4000 BYTE),"MARKET"    VARCHAR2(4000 BYTE),"BID_ASK"   VARCHAR2(4000 BYTE),"PRICE"     NUMBER);INSERT INTO tt VALUES ('Gold','US','Ask',1.1);INSERT INTO tt VALUES ('Gold','BID',1);INSERT INTO tt VALUES ('Gold','EU',1.2);INSERT INTO tt VALUES ('Gold',1.1);INSERT INTO tt VALUES ('Oil',11);INSERT INTO tt VALUES ('Oil',10);INSERT INTO tt VALUES ('Oil',12);INSERT INTO tt VALUES ('Oil',11);

我想要实现的输出类似于(确切的列标题并不重要)：

commodity   'US_BID'    'US_Ask'    'EU_BID'    'EU_Ask'Gold         1           1.1         1.1        1.2Oil          10          11          11         12

现在可以直接转动单个列：

SELECT * FROM(  SELECT * FROM tt)PIVOT(  SUM(PRICE)  FOR MARKET IN ('US','EU'))

这使：

commodity   BID_ASK 'US'    'EU'Gold        BID      1      1.1Oil         BID      10     11Oil         Ask      11     12Gold        Ask      1.1    1.2

根据我的研究,没有直接转动多列的语法.有一些相关的问题(here,here或here),但我找不到我的问题的直接答案.所以我想出了以下解决方案：

SELECT * FROM(  SELECT commodity,CONCAT(CONCAT(MARKET,'_'),BID_ASK) AS MARKET_BID_ASK,PRICE FROM tt)PIVOT(  SUM(PRICE)  FOR MARKET_BID_ASK IN ('US_BID','US_Ask','EU_BID','EU_Ask'))

这产生了完全所需的输出.但是,我不认为它是一个实用的解决方案,因为我必须输入的变量数量增长得太快(在我的真实数据集中,我想一次转动更多字段,所有这些都有许多不同的值).我知道存在dynamic pivots,但我不确定这是否适用于Excel,我也希望保持语法尽可能简单,因为用户将自己定义查询(我只是想提供一个模板)查询他们可以适应).所以我试着查询IN子句中的字段名称：

SELECT * FROM(  SELECT commodity,PRICE FROM tt)PIVOT(  SUM(PRICE)  FOR MARKET_BID_ASK IN   (    SELECT disTINCT CONCAT(CONCAT(MARKET,BID_ASK) AS MARKET_BID_ASK FROM tt  ))

我认为这样的解决方案可能是实用的,因为人们仍然可以约束所查询的变量,而不必使用子查询中的liKE条件列出所有连接选项.但是,我在这个查询中得到了“ORA-00936 – 缺少表达式”错误,尽管根据我发现的documentation,子查询在这里应该是合法的.

解决方法 您可以通过在括号中包含列和值集来在多个列上进行透视：

SELECT * FROM(  SELECT * FROM tt)PIVOT(  SUM(PRICE)  FOR (MARKET,BID_ASK)  IN (('US','BID') us_bID,('US','Ask') us_ask,('EU','BID') eu_bID,'Ask') eu_ask));commodity      US_BID     US_ASK     EU_BID     EU_ASK---------- ---------- ---------- ---------- ----------Gold                1        1.1        1.1        1.2Oil                10         11         11         12

但是在解析查询时仍然必须知道值对,如果你有很多值的组合,这就不能很好地扩展.

您唯一的选择是动态sql,正如您所怀疑的那样,除非您可以让Excel处理XML数据透视的结果 – 我认为这是不可能的.使用动态sql,如果Excel发现比数据透视查询更容易处理,您可能有一个执行查询和数据透视并返回引用游标的函数.

总结

以上是内存溢出为你收集整理的Oracle SQL：在多个列/字段上进行透视全部内容，希望文章能够帮你解决Oracle SQL：在多个列/字段上进行透视所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。