在 Oracle 中动态将行透视为列 [英] Pivoting rows into columns dynamically in Oracle

问题描述

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

I have the following Oracle 10g table called _kv:

select * from _kv

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

我想使用普通 SQL(不是 PL/SQL)将我的键转换为列，这样结果表看起来像这样:

I'd like to turn my keys into columns using plain SQL (not PL/SQL) so that the resulting table would look something like this:

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

• 查询的列数应该与表中存在的唯一 K s 一样多(没有那么多)
• 在运行查询之前无法知道可能存在哪些列.
• 我试图避免运行初始查询来以编程方式构建最终查询.
• 空白单元格可能是空值或空字符串，这并不重要.
• 我使用的是 Oracle 10g，但 11g 解决方案也可以.
• The query should have as many columns as unique Ks exist in the table (there aren't that many)
• There's no way to know what columns may exist before running the query.
• I'm trying to avoid running an initial query to programatically build the final query.
• The blank cells may be nulls or empty strings, doesn't really matter.
• I'm using Oracle 10g, but an 11g solution would also be ok.

当您知道旋转列的名称时，有很多示例，但我找不到适用于 Oracle 的通用旋转解决方案.

There are a plenty of examples out there for when you know what your pivoted columns may be called, but I just can't find a generic pivoting solution for Oracle.

谢谢！

推荐答案

Oracle 11g 提供了一个 PIVOT 操作，可以执行您想要的操作.

Oracle 11g provides a PIVOT operation that does what you want.

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

I obtained this solution from: http://orafaq.com/wiki/PIVOT

编辑——pivot xml 选项(也是 Oracle 11g)
显然，当您不知道您可能需要的所有可能的列标题时，还有一个 pivot xml 选项.(请参阅位于 XML TYPE 部分rel="noreferrer">http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html)

EDIT -- pivot xml option (also Oracle 11g)
Apparently there is also a pivot xml option for when you do not know all the possible column headings that you may need. (see the XML TYPE section near the bottom of the page located at 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 和 pivot xml 语句中的 v 更改为 max(v) 因为它应该按照评论之一中的说明进行聚合.我还添加了 in 子句，它不是 pivot 的可选子句.当然，必须在 in 子句中指定值违背了拥有完全动态数据透视表/交叉表查询的目标，这正是该问题发布者的愿望.

Changed v in the pivot and pivot xml statements to max(v) since it is supposed to be aggregated as mentioned in one of the comments. I also added the in clause which is not optional for pivot. Of course, having to specify the values in the in clause defeats the goal of having a completely dynamic pivot/crosstab query as was the desire of this question's poster.

这篇关于在 Oracle 中动态将行透视为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！