Home » SQL & PL/SQL » SQL & PL/SQL » how to achieve Anti pivoting in 10g (oracle 10g)
how to achieve Anti pivoting in 10g [message #353809] Wed, 15 October 2008 03:34 Go to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Hi,
Will bother u again for the query.
I have a table
create table test_antipivoting
(
  CUSTOMER_CD                    NUMBER(7),
  UPPER_LIMIT_AMT                NUMBER(12),
  LOWER_LIMIT_AMT                NUMBER(12),
  REGION_CD1                     NUMBER(4),
  REGION_AMT1                    NUMBER(12),
  REGION_CD2                     NUMBER(4),
  REGION_AMT2                    NUMBER(12),
  REGION_CD3                     NUMBER(4),
  REGION_AMT3                    NUMBER(12),
  REGION_CD4                     NUMBER(4),
  REGION_AMT4                    NUMBER(12),
  REGION_CD5                     NUMBER(4),
  REGION_AMT5                    NUMBER(12),
  REGION_CD6                     NUMBER(4),
  REGION_AMT6                    NUMBER(12),
  REGION_CD7                     NUMBER(4),
  REGION_AMT7                    NUMBER(12),
  REGION_CD8                     NUMBER(4),
  REGION_AMT8                    NUMBER(12),
  REGION_CD9                     NUMBER(4),
  REGION_AMT9                    NUMBER(12),
  REGION_CD10                    NUMBER(4),
  REGION_AMT10                   NUMBER(12));
  
insert into TEST_ANTIPIVOTING (CUSTOMER_CD, UPPER_LIMIT_AMT, 
LOWER_LIMIT_AMT, REGION_CD1, REGION_AMT1, REGION_CD2, 
REGION_AMT2, REGION_CD3, REGION_AMT3, REGION_CD4, REGION_AMT4,
 REGION_CD5, REGION_AMT5, REGION_CD6, REGION_AMT6, REGION_CD7, 
REGION_AMT7, REGION_CD8, REGION_AMT8, REGION_CD9, REGION_AMT9, 
REGION_CD10, REGION_AMT10)
values (1, 1000, 500, 1, 100, 2, 150, 3, 225, 4, 400, 5, 600, 6, 
100, 7, 100, 8, 20, 9, 120, 10, 100);


I need the output of this in another table
create table TEST_ANTIPIVOTING_ROWS
(
  CUSTOMER_CD     NUMBER(7),
  UPPER_LIMIT_AMT NUMBER(12),
  LOWER_LIMIT_AMT NUMBER(12),
  REGION_CD     NUMBER(4),
  REGION_AMT     NUMBER(12)
);

The data of columns from above table TEST_ANTIPIVOTING should be populated in table TEST_ANTIPIVOTING_ROWS in such a way,where value of region_Cd1,region_amt1 and so on should be populated in separate rows of region_cd and region_amt columns of TEST_ANTIPIVOTING_ROWS table.
eg
cust_cd uppper_limit_amt lower_limit_amt region_Cd region_amt
1 1000 500 1 100
1 1000 500 2 150

and so on...
Regards,
Navneet



[Updated on: Wed, 15 October 2008 03:41] by Moderator

Report message to a moderator

Re: how to achieve Anti pivoting in 10g [message #353811 is a reply to message #353809] Wed, 15 October 2008 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your lines in 80 characters.

Use a row generator, join with it and use DECODE/CASE.

Regards
Michel
Re: how to achieve Anti pivoting in 10g [message #353815 is a reply to message #353811] Wed, 15 October 2008 04:07 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
sorry Michel but I m not getting your point.
can u please clarify a little.
Regards,
Navneet
Re: how to achieve Anti pivoting in 10g [message #353819 is a reply to message #353815] Wed, 15 October 2008 04:27 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Thanks Michel for your help,
I have done that. This is my query .
select decode (lv,1,region_cd1,2,region_Cd2,3,region_Cd3,4,region_cd4,5,region_cd5
,6,region_cd6,7,region_cd7,8,region_cd8,9,region_cd9,10,region_cd10) region_cd,
decode (lv,1,region_amt1,2,region_amt2,3,region_amt3,4,region_amt4,5,region_amt5
,6,region_amt6,7,region_amt7,8,region_amt8,9,region_amt9,10,region_amt10)
from (select * from (select level lv from dual connect by level<=10)a,
test_antipivoting b)


Regards,
Navneet
Re: how to achieve Anti pivoting in 10g [message #353828 is a reply to message #353819] Wed, 15 October 2008 04:54 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Hi,
I m facing one more issue in this.
region_codes and region_amout columns are 300 each and case and decode take at most 255 arguments. any solution in this regard?

Regards
Navneet
Re: how to achieve Anti pivoting in 10g [message #353854 is a reply to message #353828] Wed, 15 October 2008 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can embed DECODE inside DECODE.

Regards
Michel
Re: how to achieve Anti pivoting in 10g [message #353855 is a reply to message #353828] Wed, 15 October 2008 06:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use CASE instead.
Re: how to achieve Anti pivoting in 10g [message #353858 is a reply to message #353855] Wed, 15 October 2008 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CASE is also limited to 255 expressions... you can embed CASE (or DECODE) inside CASE (or DECODE or the opposite).

Regards
Michel
Re: how to achieve Anti pivoting in 10g [message #353860 is a reply to message #353855] Wed, 15 October 2008 06:18 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Thanks Michel/JRowbottom,
I have accomplished my task with your help.
I had u use nested case n decode together .Even case has the limit of 255.

Thanks again,
Regards,
Navneet
Re: how to achieve Anti pivoting in 10g [message #353899 is a reply to message #353809] Wed, 15 October 2008 08:50 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Read these please, it tells you what you want to know.

Kevin Meade's blog

Example of Data Pivots in SQL (rows to columns and columns to rows)

Good luck, Kevin
Previous Topic: Spool
Next Topic: Self Join
Goto Forum:
  


Current Time: Thu Dec 08 05:59:28 CST 2016

Total time taken to generate the page: 0.07276 seconds