Home » SQL & PL/SQL » SQL & PL/SQL » Transforming rows to colums (Oracle, 10g)
| Transforming rows to colums [message #626628] |
Thu, 30 October 2014 02:26  |
 |
MeRamki
Messages: 5
Registered: October 2014
Location: Bangalore
|
Junior Member |
|
|
Hi,
I have data like head wise and period wise tax for an entity.
Eg : I have data like below in table 'tax_details'
entity_id|period_id|head_code|tax|
----------------------------------
1 1 TAX_TYPE1 10
1 1 TAX_TYPE2 10
1 2 TAX_TYPE1 10
1 2 TAX_TYPE2 10
1 3 TAX_TYPE1 10
1 3 TAX_TYPE2 10
Now i want to transform the data like below
entity_id|period_id|TAX_TYPE1|TAX_TYPE2
--------------------------------------
1 1 10 10
1 2 10 10
1 3 10 10
For this i am using query like
CREATE table tax_details_trans as
SELECT entity_id, period_id,
max((case head_code when 'TAX_TYPE1' then tax else 0 end)) GeneralTax,
max((case head_code when 'TAX_TYPE2' then tax else 0 end)) SewerageTax,
FROM tax_details
GROUP BY entity_id, period_id
/
In my tax_details table i have data for 5L entities, when we use above query to transform the data into columns for some entities the data is not correct.
Can you please suggest a way to transform into columns if my approach is wrong.
Thank you in advance.
Edited by Lalit : Added code tags
[Updated on: Thu, 30 October 2014 02:58] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Transforming rows to colums [message #626640 is a reply to message #626628] |
Thu, 30 October 2014 03:35  |
 |
sandeep_orafaq
Messages: 88
Registered: September 2014
|
Member |
|
|
I guess by 5L you mean 500K (500000) entries.
Also as mentioned by John. Please provide create table and insert scripts for data.
Please also give us a test case where your Output is not as expected. You have not described what is the expected Output. Please explain it in proper Statements.
[Updated on: Thu, 30 October 2014 03:47] Report message to a moderator |
|
|
|
| Re: Transforming rows to colums [message #626651 is a reply to message #626640] |
Thu, 30 October 2014 05:59 |
|
MeRamki
Messages: 5
Registered: October 2014
Location: Bangalore
|
Junior Member |
|
|
Thank you John Watson and sandeep_orafaq.
Yes, you are right sandeep_orafaq, 5L means 5,00,000 entities.
Create script for table tax_details
create table tax_details
( entity_id number,
period_id number, -- foreign key ref to period master table
head_code varchar2(32),
tax number(12,2));
insert script
insert into tax_details values (1,1,'TAX_TYPE1',10);
insert into tax_details values (1,1,'TAX_TYPE2',10);
insert into tax_details values (1,2,'TAX_TYPE1',10);
insert into tax_details values (1,2,'TAX_TYPE2',10);
insert into tax_details values (1,3,'TAX_TYPE1',10);
insert into tax_details values (1,4,'TAX_TYPE2',10);
Here my problem is the data is not correct in table tax_details_trans when i transform the data of 5,00,000 entities. for some entities even the value with head_code 'TAX_TYPE1' is available in table tax_details_trans it coming as zero.
|
|
|
|
|
|
|
|
|
|
| Re: Transforming rows to colums [message #626672 is a reply to message #626660] |
Thu, 30 October 2014 08:23 |
|
sandeep_orafaq
Messages: 88
Registered: September 2014
|
Member |
|
|
This seems to be interesting. I had a similar issue with distinct function in one of my update statement.
Sorry for the example like this. I do not have the case with me right now. This is just for the demonstration:
table1 has col1, col4
table2 has col2, col3
update table1 t1 set t1.col1 =
(
select distinct(t2.col2)
from table2 t2
where t2.col3 = t1.col4
)
This update staement used to work perfectly fine for fewer records, but on huge records it used update NULL for few table1.col1 thought the corresponding data used to be present in table2.
What I did: I wrote a simple procedure to do this update, where I used a cursor and calculated the distinct(table2.col2) for each value of table1.co1 and updated the same one by one.
May be other senior members can have some insight on this. Is this a bug?
[Updated on: Thu, 30 October 2014 08:24] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Transforming rows to colums [message #626675 is a reply to message #626673] |
Thu, 30 October 2014 09:00 |
|
sandeep_orafaq
Messages: 88
Registered: September 2014
|
Member |
|
|
As I remember the requirement was to populate a parent Attribute for each row based on another Attribute of the same row.
The parent Attribute could be derived from the table2 where there was link table1 and table2. table2 could return more than one value for the filter so there was distinct used.
Let me try to put in example form, again sorry for this vague way of defining it. I wish I could have that case with me 
table1
---------------
col1 col4
x
y
z
table2
---------------
col2 col3 col5
A x 1
A x 2
A x 3
B y 1
B y 2
C z 1
---------------------------------------------------------
UPDATE table1 t1
SET t1.col1 = (SELECT DISTINCT( t2.col2 )
FROM table2 t2
WHERE t2.col3 = t1.col4)
---------------------------------------------------------
After the update it should be
table1
---------------
col1 col4
A x
B y
C z
[Updated on: Thu, 30 October 2014 09:00] Report message to a moderator |
|
|
|
| Re: Transforming rows to colums [message #626700 is a reply to message #626674] |
Fri, 31 October 2014 04:36 |
|
sandeep_orafaq
Messages: 88
Registered: September 2014
|
Member |
|
|
I tried to replicate the case posted by you(OP) in Oracle 11.2.0.3 but I did not find any problem with this. This was done for 400K entities.
Can you please try the same in your system and let us know if you see any wierd behaviour.
SQL>
SQL> create table tax_details
2 ( entity_id number,
3 period_id number, -- foreign key ref to period master table
4 head_code varchar2(32),
5 tax number(12,2));
Table created
SQL>
SQL> INSERT INTO tax_details (SELECT ROWNUM, 1, 'TAX_TYPE1', 10 FROM dual CONNECT BY LEVEL <=400000);
400000 rows inserted
SQL> INSERT INTO tax_details (SELECT ROWNUM, 1, 'TAX_TYPE2', 10 FROM dual CONNECT BY LEVEL <=400000);
400000 rows inserted
SQL> INSERT INTO tax_details (SELECT ROWNUM, 2, 'TAX_TYPE1', 10 FROM dual CONNECT BY LEVEL <=400000);
400000 rows inserted
SQL> INSERT INTO tax_details (SELECT ROWNUM, 2, 'TAX_TYPE2', 10 FROM dual CONNECT BY LEVEL <=400000);
400000 rows inserted
SQL> INSERT INTO tax_details (SELECT ROWNUM, 3, 'TAX_TYPE1', 10 FROM dual CONNECT BY LEVEL <=400000);
400000 rows inserted
SQL> INSERT INTO tax_details (SELECT ROWNUM, 3, 'TAX_TYPE2', 10 FROM dual CONNECT BY LEVEL <=400000);
400000 rows inserted
SQL> commit;
Commit complete
SQL> select * from TAX_DETAILS t WHERE entity_id <=2 ORDER BY t.entity_id,t.period_id,t.head_code;
ENTITY_ID PERIOD_ID HEAD_CODE TAX
---------- ---------- -------------------------------- --------------
1 1 TAX_TYPE1 10.00
1 1 TAX_TYPE2 10.00
1 2 TAX_TYPE1 10.00
1 2 TAX_TYPE2 10.00
1 3 TAX_TYPE1 10.00
1 3 TAX_TYPE2 10.00
2 1 TAX_TYPE1 10.00
2 1 TAX_TYPE2 10.00
2 2 TAX_TYPE1 10.00
2 2 TAX_TYPE2 10.00
2 3 TAX_TYPE1 10.00
2 3 TAX_TYPE2 10.00
12 rows selected
SQL>
SQL> CREATE table tax_details_trans as
2 SELECT entity_id, period_id,
3 max((case head_code when 'TAX_TYPE1' then tax else 0 end)) GeneralTax,
4 max((case head_code when 'TAX_TYPE2' then tax else 0 end)) SewerageTax
5 FROM tax_details
6 GROUP BY entity_id, period_id;
Table created
SQL> SELECT * FROM tax_details_trans WHERE entity_id <=2 ORDER BY entity_id,period_id;
ENTITY_ID PERIOD_ID GENERALTAX SEWERAGETAX
---------- ---------- ---------- -----------
1 1 10 10
1 2 10 10
1 3 10 10
2 1 10 10
2 2 10 10
2 3 10 10
6 rows selected
SQL> SELECT * FROM tax_details_trans WHERE GeneralTax=0 OR SewerageTax=0
2 /
ENTITY_ID PERIOD_ID GENERALTAX SEWERAGETAX
---------- ---------- ---------- -----------
SQL> SELECT count(1) FROM tax_details_trans WHERE GeneralTax=0 OR SewerageTax=0
2 /
COUNT(1)
----------
0
SQL> SELECT count(1) FROM tax_details_trans WHERE GeneralTax!=10 OR SewerageTax!=10
2 /
COUNT(1)
----------
0
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 17:53:33 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
