| splitting 1 output row into 3 output rows [message #596660] |
Wed, 25 September 2013 09:01  |
|
|
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  |
pablolee
Messages: 2882
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 #596665 is a reply to message #596664] |
Wed, 25 September 2013 10:02 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
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;
|
|
|
|
 Re: splitting 1 output row into 3 output rows [message #596670 is a reply to message #596660] |
Wed, 25 September 2013 10:40 |
 |
Michel Cadot
Messages: 68647
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
