我在Oracle表(tab1)中有以下示例数据,我试图将行转换为列.我知道如何在一列上使用Oracle pivot.但是可以将它应用于多个列吗？
样本数据：
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) )
以上查询给出了以下结果
A B C 2 1 1
我可以将sum(*)替换为sum(weight)或sum(height)来转动高度或重量.我想要做的,但我不能做,是在一个查询中转动所有三个(计数,重量和高度).
可以用枢轴完成吗？
解决方法
如
the documentation shows,您可以有多个聚合函数子句.所以你可以这样做：
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
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(height) as ht
for type in ('A' as A,'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
