具有附加(非聚合)列的 Oracle SQL xml 透视

Question

我正在寻找一种在 xml 透视查询中包含非聚合值的方法。 我的代码如下所示：

select * from (
select country, month from travel
where <parameters, as far as i know irrelevant to the discussion>
pivot xml (
count(month) travellers,
for country in (select country from table_a where <parameters>)
)

查询一个表，其中每行表示一个旅行人员，以便月计数将返回该月旅行的人数。

此查询返回以下 xml：

<PivotSet>
    <item>
        <column name = "COUNTRY">AE</column>
        <column name = "TRAVELLERS">1</column>
    </item>
    <item>
        <column name = "COUNTRY">AL</column>
        <column name = "TRAVELLERS">1</column>
    </item>
    <item>
        <column name = "COUNTRY">BE</column>
        <column name = "TRAVELLERS">2</column>
    </item>
    <item>
    ...
    </item>
</PivotSet>

我想要的是获取 b 和 c 的非聚合值，我希望它看起来像：

select * from (
select country, month from travel
where <parameters, irrelevant to the discussion>
pivot xml (
count(month) travellers,
for country, month in (select country, month from table_a where <parameters>)
)

但这不起作用，因为 Pivot 似乎只允许一列的非聚合值。

我希望看到返回以下 xml：

<PivotSet>
    <item>
        <column name="MONTH">JANUARY</column>
        <column name = "COUNTRY">AE</column>
        <column name = "TRAVELLERS">1</column>
    </item>
    <item>
        <column name="MONTH">FEBRUARY</column>
        <column name = "COUNTRY">AL</column>
        <column name = "TRAVELLERS">1</column>
    </item>
    <item>
        <column name="MONTH">MARCH</column>
        <column name = "COUNTRY">BE</column>
        <column name = "TRAVELLERS">2</column>
    </item>
    <item>
    ...
    </item>
</PivotSet>

我有哪些选择来实现这项工作？我是否误解了枢轴函数的工作原理？

Answer 1

据我所知，从问题中的代码片段来看，忽略我认为您引入的问题的错误; 您只需要在要透视的两列周围添加括号：

select *
from (
  select country, month from travel
  where <parameters, as far as i know irrelevant to the discussion>
)
pivot xml (
  count(*) travellers
  for (country, month) in (select country from table_a where <parameters>)
)

透视子句语法图显示，您可以有一个表达式，也可以有一个逗号分隔的括号中的表达式列表。如果没有括号，它不会期望第二个表达式(, month(并抛出"ORA-01738：缺少IN关键字"，因为这是它期望接下来看到的内容。

使用 CTE 进行演示，并假设您从同一个表中获取不同的月份/国家/地区，为简单起见：

-- CTE for dummy data
with travel (country, month) as (
  select 'US', date '2017-01-01' from dual connect by level <= 3
  union all select 'US', date '2017-02-01' from dual connect by level <= 1
  union all select 'France', date '2017-01-01' from dual connect by level <= 1
  union all select 'France', date '2017-02-01' from dual connect by level <= 5
  union all select 'Spain', date '2017-01-01' from dual connect by level <= 5
  union all select 'Spain', date '2017-02-01' from dual connect by level <= 2
)
-- actual query
select xmlserialize(document country_month_xml indent size=2)
from (
  select country, month from travel
)
pivot xml (
  count(*) travellers
  for (country, month) in (select distinct country, month from travel)
);

得到：

<PivotSet>
  <item>
    <column name="COUNTRY">France</column>
    <column name="MONTH">2017-01-01</column>
    <column name="TRAVELLERS">1</column>
  </item>
  <item>
    <column name="COUNTRY">France</column>
    <column name="MONTH">2017-02-01</column>
    <column name="TRAVELLERS">5</column>
  </item>
  <item>
    <column name="COUNTRY">Spain</column>
    <column name="MONTH">2017-01-01</column>
    <column name="TRAVELLERS">5</column>
  </item>
  <item>
    <column name="COUNTRY">Spain</column>
    <column name="MONTH">2017-02-01</column>
    <column name="TRAVELLERS">2</column>
  </item>
  <item>
    <column name="COUNTRY">US</column>
    <column name="MONTH">2017-01-01</column>
    <column name="TRAVELLERS">3</column>
  </item>
  <item>
    <column name="COUNTRY">US</column>
    <column name="MONTH">2017-02-01</column>
    <column name="TRAVELLERS">1</column>
  </item>
</PivotSet>

我添加了一个XMLSerialize调用，只是为了使其更具可读性...