ORACLE将数据透视表移到行

本文介绍了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

SQL DEMO

此查询基于此处