Home » SQL & PL/SQL » SQL & PL/SQL » reverse the cross tab in oracle
reverse the cross tab in oracle [message #188275] Thu, 17 August 2006 14:21 Go to next message
samyugtha
Messages: 14
Registered: November 2005
Junior Member
Hi

My table structure is

Acc_Stat_tab
(Lessthan_Balance_$10_Accts,
Lessthan_Balance_$10_Accts_Bal,
Lessthan_Balance_$100_Accts,
Lessthan_Balance_$100_Accts_Bal,
Lessthan_Balance_$1000_Accts,
Lessthan_Balance_$1000_Accts_Bal)

I want to show like,

Balance_Range Accounts Balance
Less than $10 100 134
Less than $100 10 13409
Less than $1000 13 134909

How to write a single SQL query with out using UNION ALL?

Thanks in advance.

Sam
Re: reverse the cross tab in oracle [message #188312 is a reply to message #188275] Thu, 17 August 2006 21:35 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There are other ways, but UNION ALL is the simplest.

The two ways that come to mind are:
1. Cross Join to (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3) and use a DECODE(LEVEL,1,L10,2,L100, ...) to get the appropriate measure.

2. Cast the row to a VARRAY Collection Type.

Both of these methods will use about the same amount of I/O, but they also both use unusual syntax that few will understand. Better in my opinion to use common syntax unless there is a critical performance problem.

Ross Leishman
Re: reverse the cross tab in oracle [message #188314 is a reply to message #188312] Thu, 17 August 2006 21:54 Go to previous messageGo to next message
samyugtha
Messages: 14
Registered: November 2005
Junior Member
Hi
Thanks for your reply. But I couldnt understand. if you can explain with simple example, it would be better.

Thanks in Advance.
Re: reverse the cross tab in oracle [message #188336 is a reply to message #188314] Fri, 18 August 2006 02:17 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That is my point exactly - the other methods are complex and difficult to understand. They utilise functionality that is not commonly understood by the average programmer.

Both of these methods have been described in more detail before in this forum. If you are really persistent, a search will turn them up. But I wouldn't bother if I were you.

Just use UNION ALL.

Ross Leishman
Previous Topic: Parent child relationship table
Next Topic: Bulk Collect Error
Goto Forum:
  


Current Time: Fri Dec 09 00:19:41 CST 2016

Total time taken to generate the page: 0.05363 seconds