在 Oracle 行的多个列上使用数据透视

本教程将介绍在 Oracle 行的多个列上使用数据透视的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

在 Oracle 行的多个列上使用数据透视 教程 第1张

问题描述

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?

Sample data:

Type  weight  heightA  5010A  6012B  408C  3015

My intended output:

A-count B-count C-count A-weight B-weight C-weight A-height B-height C-height2 1 1 11040 30 22 8  15

What I can do:

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 C2 1 1

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.

Can it be done using pivot?

解决方案

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  11022 140 8 13015 

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  110403022 815 

SQL Fiddle.

好了关于在 Oracle 行的多个列上使用数据透视的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。