SQL 在 Oracle 中动态将行透视为列

Pivoting rows into columns dynamically in Oracle

提问by ojosilva

I have the following Oracle 10g table called _kv:

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

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)
• 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.

• 查询应该有与表中K存在的unique 一样多的列（没有那么多）
• 在运行查询之前无法知道可能存在哪些列。
• 我试图避免运行初始查询以编程方式构建最终查询。
• 空白单元格可能是空值或空字符串，并不重要。
• 我使用的是 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!

谢谢！

回答by dave

Oracle 11g provides a PIVOToperation 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 xmloption for when you do not know all the possible column headings that you may need. (see the XML TYPEsection 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 vin the pivotand pivot xmlstatements to max(v)since it is supposed to be aggregated as mentioned in one of the comments. I also added the inclause which is not optional for pivot. Of course, having to specify the values in the inclause defeats the goal of having a completely dynamic pivot/crosstab query as was the desire of this question's poster.

Edit2：v在pivot和pivot xml语句中更改为，max(v)因为它应该按照其中一条评论中的提到进行聚合。我还添加了in对于pivot. 当然，必须在in子句中指定值会破坏拥有完全动态的数据透视表/交叉表查询的目标，这正是该问题发布者的愿望。

回答by eniacAvenger

To deal with situations where there are a possibility of multiple values (v in your example), I use PIVOTand 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.

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

回答by Herbert Yu

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'
  )
);

回答by Sarath Avanavu

First of all, dynamically pivot using pivot xmlagain 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 usingpivot xml再次需要解析。我们还有另一种方法，通过将列名存储在变量中并将它们传递到动态 sql 中，如下所示。

Consider we have a table like below.

考虑我们有一个如下表。

If we need to show the values in the column YRas 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

结果