Home » SQL & PL/SQL » SQL & PL/SQL » splitting 1 output row into 3 output rows (Oracle 11g)
splitting 1 output row into 3 output rows [message #596660] Wed, 25 September 2013 09:01 Go to next message
rajeshanasuri
Messages: 3
Registered: December 2012
Location: HYDERABAD
Junior Member

Hi all,

Currently I have a requirement where I need to create 2 more output rows using each result row.

In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.

--Create tables charges 
create table charges
(
charge_type varchar2(10) ,
charge number
);

--Create table taxes
create table taxes
(
tax_type varchar2(10) ,
tax_percentage number
);

--Populate charges table
Insert into charges values('charge1', '100');
Insert into charges values('charge2', '200');
commit;

--Populate taxes table
Insert into taxes values('tax1', '10');
Insert into taxes values('tax2', '20');
commit;


My expected output should be like below:
 
Item_type            amount          
-------------------- ---------- 
charge1               100       
Charge1_tax1          10        
Charge1_tax2          20        
charge2               200       
Charge2_tax1          20        
Charge2_tax2          40        


Can anyone help me how I can achieve the expected output using a single sql query

[Updated on: Wed, 25 September 2013 09:29]

Report message to a moderator

Re: splitting 1 output row into 3 output rows [message #596662 is a reply to message #596660] Wed, 25 September 2013 09:37 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Hi, welcome to OraFaq.
Thanks for supplying a test case and expected results (it's a rare thing, so thanks)
Quote:
My expected output should be like below:

Why? There doesn't appear to be anything in your data that will allow a database to connect the two collections of information. Also, where do the values for Charge2_tax1 and Charge2_Tax2 come from?
Re: splitting 1 output row into 3 output rows [message #596663 is a reply to message #596662] Wed, 25 September 2013 09:45 Go to previous messageGo to next message
rajeshanasuri
Messages: 3
Registered: December 2012
Location: HYDERABAD
Junior Member

Charge2_tax1 and Charge2_tax2 came from combining charge_type column from charges table and tax_type column from taxes as I need to apply 2 taxes on same charge and show in the output along with the actual charge.

Re: splitting 1 output row into 3 output rows [message #596664 is a reply to message #596663] Wed, 25 September 2013 09:51 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Combining? Combining in what way? Your requirements need to be much clearer. Also, you did not answer my first question.

I have a feeling that, in attempting to simplify your requirements, you have, in fact, oversimplified them.

[Updated on: Wed, 25 September 2013 09:55]

Report message to a moderator

Re: splitting 1 output row into 3 output rows [message #596665 is a reply to message #596664] Wed, 25 September 2013 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
BTW, NUMBER datatype should NOT be enclosed in single quote marks.

what should results from sample data like below?

--Populate charges table
Insert into charges values('charge1', 100);
Insert into charges values('charge2', 200);
Insert into charges values('charge3', 300);
Insert into charges values('charge4', 400);
commit;

--Populate taxes table
Insert into taxes values('tax1', 10);
Insert into taxes values('tax2', 20);
Insert into taxes values('tax3', 30);
Insert into taxes values('tax4', 40);
commit;
icon14.gif  Re: splitting 1 output row into 3 output rows [message #596670 is a reply to message #596660] Wed, 25 September 2013 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from charges order by 1;

CHARGE_TYP     CHARGE
---------- ----------
charge1           100
charge2           200
charge3           300
charge4           400

4 rows selected.

SQL> select * from taxes order by 1;

TAX_TYPE   TAX_PERCENTAGE
---------- --------------
tax1                   10
tax2                   20
tax3                   30
tax4                   40

4 rows selected.

SQL> with 
  2    tax_data as (
  3      select '_'||tax_type tax_type, tax_percentage from taxes
  4      union all
  5      select null, 100 from dual
  6    )
  7  select charge_type||tax_type, charge*tax_percentage/100 amount
  8  from charges, tax_data
  9  order by 1
 10  /

CHARGE_TYPE||TAX_TYPE     AMOUNT
--------------------- ----------
charge1                      100
charge1_tax1                  10
charge1_tax2                  20
charge1_tax3                  30
charge1_tax4                  40
charge2                      200
charge2_tax1                  20
charge2_tax2                  40
charge2_tax3                  60
charge2_tax4                  80
charge3                      300
charge3_tax1                  30
charge3_tax2                  60
charge3_tax3                  90
charge3_tax4                 120
charge4                      400
charge4_tax1                  40
charge4_tax2                  80
charge4_tax3                 120
charge4_tax4                 160

20 rows selected.

[Updated on: Wed, 25 September 2013 10:40]

Report message to a moderator

Re: splitting 1 output row into 3 output rows [message #596760 is a reply to message #596670] Thu, 26 September 2013 03:58 Go to previous message
rajeshanasuri
Messages: 3
Registered: December 2012
Location: HYDERABAD
Junior Member

Thanks all for your responses.

@Michel Cadot.. thanks for your solution. It solved our problem.
Previous Topic: Modularisation of Plsql packages
Next Topic: V$ tables interrogate
Goto Forum:
  


Current Time: Wed Apr 16 23:31:53 CDT 2014

Total time taken to generate the page: 0.09070 seconds