在 Oracle 中动态地将行转为列

Pivoting rows into columns dynamically in Oracle(在 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 列一样多(没有那么多)
• 在运行查询之前无法知道可能存在哪些列.
• 我试图避免运行初始查询来以编程方式构建最终查询.
• 空白单元格可能是空值或空字符串，并不重要.
• 我使用的是 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

EDIT -- 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 中动态地将行转为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！