在Oracle中动态地将行旋转到列中

I have the following Oracle 10g table called _kv:

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

select * from _kv

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

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:

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

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

The query should have as many columns as unique Ks exist in the table (there aren't that many)
查询应该具有与表中存在的唯一Ks一样多的列（没有那么多）
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 10g，但11g解决方案也可以。

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的通用透视解决方案。

Thanks!

谢谢！

4 个解决方案

#1

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

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

Oracle 11g solution

Oracle 11g解决方案

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

(Note: I do not have a copy of 11g to test this on so I have not verified its functionality)

（注意：我没有11g的副本来测试这个，所以我还没有验证它的功能）

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

我从http://orafaq.com/wiki/PIVOT获得了这个解决方案

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)

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

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

(Note: As before I do not have a copy of 11g to test this on so I have not verified its functionality)

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

Edit2: 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.

Edit2：将pivot和pivot xml语句中的v更改为max（v），因为它应该按照其中一条注释中的说明进行聚合。我还添加了in子句，它对于pivot来说不是可选的。当然，必须在in子句中指定值会使得拥有完全动态的pivot / crosstab查询的目标失败，就像这个问题的海报一样。

#2

To deal with situations where there are a possibility of multiple values (v in your example), I use PIVOT and LISTAGG:

为了处理可能存在多个值的情况（在您的示例中为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;

Since you want dynamic values, use dynamic SQL and pass in the values determined by running a select on the table data before calling the pivot statement.

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

#3

Happen to have a task on pivot. Below works for me as tested just now on 11g:

发生在枢轴上的任务。以下对11g的测试仅适用于我：

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

#4

First of all, dynamically pivot using pivot xml again needs to be parsed. We have another way of doing this by storing the column names in a variable and passing them in the dynamic sql as below.

首先，需要再次使用pivot xml动态调整。我们有另一种方法，通过将列名存储在变量中并将它们传递给动态sql，如下所示。

Consider we have a table like below.

考虑我们有一个如下表格。

If we need to show the values in the column YR as column names and the values in those columns from QTY, then we can use the below code.

如果我们需要将列YR中的值显示为列名和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;
/

RESULT

结果

#1