在 Oracle 中动态地将行转换为列

Question

我有以下名为 _kv 的 Oracle 10g 表：

select * from _kv

ID       K       V
----     -----   -----
  1      name    Bob
  1      age     30
  1      gender  male
  2      name    Susan
  2      status  married

我想使用普通 SQL（不是 PL/SQL）将键转换为列，以便生成的表看起来像这样：

ID       NAME    AGE    GENDER  STATUS
----     -----   -----  ------  --------
  1      Bob      30     male 
  2      Susan                   married

• 查询应该具有与唯一的列一样多的列 <表中存在 code>K（没有那么多）
• 在运行查询之前无法知道可能存在哪些列。
• 我试图避免运行初始查询以编程方式构建最终查询。
• 空白单元格可能是空值或空字符串，这并不重要。
• 我使用的是 Oracle 10g，但 11g 解决方案也可以。

当您知道旋转列的名称时，有很多示例可供参考，但我就是找不到适用于 Oracle 的通用旋转解决方案。

谢谢！

Answer 1

Oracle 11g 提供了一个PIVOT 操作来完成您想要的操作。

Oracle 11g 解决方案

select * from
(select id, k, v from _kv) 
pivot(max(v) for k in ('name', 'age', 'gender', 'status')

（注意：我没有 11g 的副本来测试它，因此我尚未验证其功能）

我从以下位置获得了此解决方案：http://orafaq.com/wiki/PIVOT

编辑 -- 数据透视 xml 选项（也是 Oracle 11g）
显然，当您不知道可能需要的所有可能的列标题时，还有一个 pivot xml 选项。 （请参阅页面底部附近的 XML 类型 部分，位于 http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html）

select * from
(select id, k, v from _kv) 
pivot xml (max(v)
for k in (any) )

（注意：和以前一样，我没有11g 的副本来测试它，所以我还没有验证其功能）

Edit2： 更改了 pivot 和 中的 v >pivot xml 语句到 max(v) 因为它应该按照评论之一中提到的那样进行聚合。我还添加了 in 子句，该子句对于 pivot 来说不是可选的。当然，必须在 in 子句中指定值违背了完全动态的数据透视表/交叉表查询的目标，而这正是该问题发布者的愿望。

Answer 2

为了处理可能存在多个值的情况（示例中为 v），我使用 PIVOT 和 LISTAGG ：

SELECT * FROM
(
  SELECT id, k, v
  FROM _kv 
)
PIVOT 
(
  LISTAGG(v ,',') 
  WITHIN GROUP (ORDER BY k) 
  FOR k IN ('name', 'age','gender','status')
)
ORDER BY id;

由于您需要动态值，因此请使用动态 SQL 并传递在调用数据透视语句之前对表数据运行 select 确定的值。

Answer 3

碰巧有一个关于枢轴的任务。下面是我刚刚在 11g 上测试的结果：

select * from
(
  select ID, COUNTRY_NAME, TOTAL_COUNT from ONE_TABLE 
) 
pivot(
  SUM(TOTAL_COUNT) for COUNTRY_NAME in (
    'Canada', 'USA', 'Mexico'
  )
);

Answer 4

首先，需要再次解析使用pivot xml动态透视。我们还有另一种方法来做到这一点，即将列名存储在变量中并将它们传递到动态 sql 中，如下所示。

考虑我们有一个如下表。

中的值显示为列名称，以及这些列中 QTY 中的值，那么我们可以使用下面的代码。

declare
  sqlqry clob;
  cols clob;
begin
  select listagg('''' || YR || ''' as "' || YR || '"', ',') within group (order by YR)
  into   cols
  from   (select distinct YR from EMPLOYEE);


  sqlqry :=
  '      
  select * from
  (
      select *
      from EMPLOYEE
  )
  pivot
  (
    MIN(QTY) for YR in (' || cols  || ')
  )';

  execute immediate sqlqry;
end;
/

结果