将pivot中的XML列数据显示为单独的列

Question

我有一个结构化的表，

列:month_yyyy, account_id, account_name, revenue

我想透视表中的数据，使其显示为

Columns: account_id, account_name, month_yyyy1 (revenue)， month_yyyy2 (revenue)…monthyyyy_n(收入)

我做了这个，

    select * from ( 
SELECT month_yyyy,
       account_id,
       account_name,
       revenue
  FROM table
   order by to_date(month_yyyy, 'month yyyy')
)
pivot xml(
    sum (revenue) as revenue for (month_yyyy) in (select distinct month_yyyy from table) 
  ) 
order by account_name;

给出的数据类似于透视xml列

<PivotSet><item><column name = "MONTH_YYYY">April 2012</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">August 2012</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">December 2012</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">Decmber 2011</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">February 2012</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">February 2013</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">January 2012</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">January 2013</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">July 2012</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">June 2012</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">March 2012</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">March 2013</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">May 2012</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">November 2011</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">November 2012</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">October 2012</column><column name = "REVENUE">0</column></item><item><column name = "MONTH_YYYY">September 2012</column><column name = "REVENUE">0</column></item></PivotSet>

如何创建一个视图，选择并按月显示xml列数据作为单独的列?我想要实现，

account_id, account_name, month_yyyy1 (revenue)， month_yyyy2 (revenue)…monthyyyy_n(收入)

感谢您提供的任何帮助，如果需要进一步的信息，请告诉我。

Answer 1

似乎不能在in子句中与select一起使用pivot

我在sql fiddle上测试了它，你需要给出一个值列表来pivot。也许您可以使用pl/sql函数将结果列转换为逗号分隔的列表，但不确定pivot是否可用。

create table tab
( month_yyyy varchar2(20)
, account_id number
, account_name varchar2(20)
, revenue number
);
insert into tab values ('januari_2013', 1, 'acc one', 10);
insert into tab values ('januari_2013', 2, 'acc two', 20);
insert into tab values ('februari_2013', 1, 'acc one', 100);
insert into tab values ('februari_2013', 2, 'acc two', 200);
insert into tab values ('march_2013', 1, 'acc one', 1000);
insert into tab values ('march_2013', 2, 'acc two', 2000);
select *
from   tab
-- this does not work: pivot ( sum(revenue) FOR month_yyyy IN (select distinct month_yyyy from tab) )
-- this works:
pivot ( sum(revenue) FOR month_yyyy IN ('januari_2013', 'februari_2013', 'march_2013') )