在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列一样多(没有很多)
• 在运行查询之前，无法知道可能存在哪些列.
• 我试图避免运行初始查询以编程方式构建最终查询.
• 空白单元格可以为null或空字符串，并不重要.
• 我正在使用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

编辑-枢轴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 :将v和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屋！