Home » SQL & PL/SQL » SQL & PL/SQL » Query to PIVOT (oracle 11g)
Query to PIVOT [message #602135] Fri, 29 November 2013 16:09 Go to next message
srinivas.k2005
Messages: 303
Registered: August 2006
Senior Member
Hi,

I have table transaction_t table and it can have maximum three fee types and min is no fee type records.
In below case i have considered for one transaction id we have 3 fee types,
Fee types are stores in separate table fee_t.
I need all three fee values to come inline with transaction record if any fee exists otherwise just show null in all those fields

Was checking with PIVOT but could not get correct idea, so need your help on this

Create table transaction_t ( id number, namer varchar2(1000));

insert into transaction_t values ( 1, 'Test1');

Create table transaction_ty ( id_fk number, sub_namer varchar2(1000));

insert into transaction_ty values ( 1, 'Test1_sub');
insert into transaction_ty values ( 1, 'Test2_sub');

Create table fees_t ( id_fk number, amt number, typep varchar(100), per number);

insert into fees_t values (1, 100,'A',3);
insert into fees_t values (1, 110,'B',4);
insert into fees_t values (1, 108,'C',5);

Need output as :

Row#	NAMER	SUB_NAMER	AMT1	TYPEP1	PER1	AMT2	TYPEP2	PER2	AMT3	TYPEP3	PER3

1	Test1	Test1_sub	100	A	3	110	B	4	108	C	5
2	Test1	Test2_sub	100	A	3	110	B	4	108	C	5
Assume if transaction does not have any fees
3       Test2   Test3_sub       NULL    NULL    NULL     NULL   NULL    NULL    NULL    NULL    NULL
Assume if you have one fee type
4       Test4   Test4_sub       50      D       6        NULL    NULL    NULL    NULL    NULL   NULL


Thanks
SRK
Re: Query to PIVOT [message #602141 is a reply to message #602135] Sat, 30 November 2013 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why there is not:
5       Michel   CADOT       NULL    NULL    NULL     NULL   NULL    NULL    NULL    NULL    NULL

Re: Query to PIVOT [message #602143 is a reply to message #602135] Sat, 30 November 2013 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With a correct test case:
drop table transaction_t ;
Create table transaction_t ( id number, namer varchar2(5));

insert into transaction_t values ( 1, 'Test1');
insert into transaction_t values ( 2, 'Test2');
insert into transaction_t values ( 4, 'Test4');

drop table transaction_ty ;
Create table transaction_ty ( id_fk number, sub_namer varchar2(9));

insert into transaction_ty values ( 1, 'Test1_sub');
insert into transaction_ty values ( 1, 'Test2_sub');
insert into transaction_ty values ( 2, 'Test3_sub');
insert into transaction_ty values ( 4, 'Test4_sub');

drop table fees_t ;
Create table fees_t ( id_fk number, amt number, typep varchar2(6), per number);

insert into fees_t values (1, 100,'A',3);
insert into fees_t values (1, 110,'B',4);
insert into fees_t values (1, 108,'C',5);
insert into fees_t values (4, 50,'D',6);

commit;

SQL> set numwidth 5
SQL> select namer, sub_namer, 
  2         max(decode(rn, 1, amt)) amt1,
  3         max(decode(rn, 1, typep)) typep1,
  4         max(decode(rn, 1, per)) per1,
  5         max(decode(rn, 2, amt)) amt2,
  6         max(decode(rn, 2, typep)) typep2,
  7         max(decode(rn, 2, per)) per2,
  8         max(decode(rn, 3, amt)) amt3,
  9         max(decode(rn, 3, typep)) typep3,
 10         max(decode(rn, 3, per)) per3
 11  from ( select t.namer, ty.sub_namer, f.amt, f.typep, f.per,
 12                row_number () over (partition by t.namer, ty.sub_namer order by null) rn
 13         from transaction_t  t, transaction_ty  ty, fees_t  f
 14         where ty.id_fk = t.id
 15           and f.id_fk (+) = t.id )
 16  group by namer, sub_namer
 17  order by 1, 2
 18  /
NAMER SUB_NAMER  AMT1 TYPEP1  PER1  AMT2 TYPEP2  PER2  AMT3 TYPEP3  PER3
----- --------- ----- ------ ----- ----- ------ ----- ----- ------ -----
Test1 Test1_sub   110 B          4   108 C          5   100 A          3
Test1 Test2_sub   108 C          5   110 B          4   100 A          3
Test2 Test3_sub
Test4 Test4_sub    50 D          6

4 rows selected.

[Updated on: Sat, 30 November 2013 00:48]

Report message to a moderator

Re: Query to PIVOT [message #602146 is a reply to message #602143] Sat, 30 November 2013 02:43 Go to previous message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
Or, with it being 11g:
select *
from
  ( select t.namer namer, ty.sub_namer subnamer, f.amt amt, f.typep typep, f.per per,
                 row_number () over (partition by t.namer, ty.sub_namer order by null) rn
          from transaction_t  t, transaction_ty  ty, fees_t  f
          where ty.id_fk = t.id
            and f.id_fk (+) = t.id )
pivot (max(per) as per, max(amt) as amt, max(typep) as typep
       for rn in (1, 2, 3 ))

(OP can figure out how to sort the column names
Previous Topic: how to write this update query?
Next Topic: Multiple groupings in SQL
Goto Forum:
  


Current Time: Wed Oct 22 23:35:45 CDT 2014

Total time taken to generate the page: 0.12473 seconds