ORACLE将数据透视表移到行 [英] ORACLE unpivot columns to rows
本文介绍了ORACLE将数据透视表移到行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
ORACLE 10 我在这里读了几篇文章,但没有想到一个简单的解决方案
ORACLE 10 Hi, I was reading several posts here and i did not come up to a simple solution
我有以下数据:
Transacion_ID GROSS_AMOUNT DISCOUNT_AMOUNT
1, 10 , -1
2, 1002 , -14
3, 36 , -5
而且我需要毫无疑问地得到
And I need to unpivot to get
Transacion_ID TYPE AMOUNT
1, GROSS , 10
1, DISC , -1
2, GROSS , 1002
2, DISC , -14
3, GROSS , 36
3, DISC , -5
我的第一种方法是将其拆分为两个查询,然后拆分为UNION ALL两种结果,但是此操作每5小时运行一次,并且有多个联接,因此拆分意味着执行时间几乎重复.我在oracle 10中寻找诸如pivot/unpivot之类的东西.
My first approach was to split this in two queries and then just UNION ALL boths results, but this run every 5 hours and has several joins, so spliting means nearly duplicate exec time. I was looking for something like pivot/unpivot in oracle 10.
推荐答案
尝试一下
Select * From (
Select
Transacion_ID,
Case When C.lvl = 1 Then 'GROSS'
When C.lvl = 2 Then 'DISC'
End TYPE,
Case When C.lvl = 1 Then GROSS_AMOUNT
When C.lvl = 2 Then DISCOUNT_AMOUNT
End AMOUNT
From T
cross join (select level lvl from dual connect by level<=2) c
) where amount is not null
order by 1
此查询基于此处
这篇关于ORACLE将数据透视表移到行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文