在 Oracle 行的多列上使用数据透视表 [英] Using pivot on multiple columns of an Oracle row

问题描述

我在 Oracle 表 (tab1) 中有以下示例数据，我正在尝试将行转换为列.我知道如何在一列上使用 Oracle 数据透视表.但是是否可以将其应用于多列?

I have the following sample data in an Oracle table (tab1) and I am trying to convert rows to columns. I know how to use Oracle pivot on one column. But is it possible to apply it to multiple columns?

样本数据:

Type  weight  height  
A     50      10  
A     60      12  
B     40      8  
C     30      15  

我的预期输出:

A-count B-count C-count A-weight B-weight C-weight A-height B-height C-height  
2       1       1       110      40       30       22       8        15  

我能做什么:

with T AS 
(select type, weight from tab1 )
select * from T
PIVOT (
count(type)
for type in (A, B, C, D,E,F)
)  

上面的查询给了我下面的结果

The above query gives me the below result

A B C  
2 1 1  

我可以用 sum(weight) 或 sum(height) 替换 count(*) 以旋转高度或重量.我想做但我不能做的是在一个查询中以所有三个(计数、重量和高度)为中心.

I can replace count(*) with sum(weight) or sum(height) to pivot height or weight. What I am looking to do, but I can't do, is pivot on all three (count, weight and height) in one query.

可以使用pivot来完成吗?

Can it be done using pivot?

推荐答案

As 文档显示，您可以有多个聚合函数子句.所以你可以这样做:

As the documentation shows, you can have multiple aggregate function clauses. So you can do this:

select * from (
  select * from tab1
)
pivot (
  count(type) as ct, sum(weight) as wt, sum(height) as ht
  for type in ('A' as A, 'B' as B, 'C' as C)
);

A_CT A_WT A_HT B_CT B_WT B_HT C_CT C_WT C_HT
---- ---- ---- ---- ---- ---- ---- ---- ----
   2  110   22    1   40    8    1   30   15 

如果您希望列按您显示的顺序排列，请添加另一个级别的子查询:

If you want the columns in the order you showed then add another level of subquery:

select a_ct, b_ct, c_ct, a_wt, b_wt, c_wt, a_ht, b_ht, c_ht
from (
  select * from (
    select * from tab1
  )
  pivot (
    count(type) as ct, sum(weight) as wt, sum(height) as ht
    for type in ('A' as A, 'B' as B, 'C' as C)
  )
);

A_CT B_CT C_CT A_WT B_WT C_WT A_HT B_HT C_HT
---- ---- ---- ---- ---- ---- ---- ---- ----
   2    1    1  110   40   30   22    8   15 

SQL 小提琴.

这篇关于在 Oracle 行的多列上使用数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！