I love carpentry. My favorite TV shows include "THIS OLD
HOUSE" and "THE NEW YANKEE WORKSHOP". I can make wooden
rotating tables. But how can you rotate RDBMS tables?
We had a specific requirement to rotate a table and present the
one to many relation between two tables as one to one relation and present all the
matching records in a single row. It is quite an interesting and challenging task.
To simulate and further explain the requirement, I created two
tables, one with products information and the second with parts information.
The products and parts tables have one to many relation. When I join the
products table with the parts table, I get multiple rows for each product with
matching parts information.
I created two tables, products_jp and parts_jp, and inserted
records into the tables.
Create table products_jp(productno number,product_name varchar2(20)); Create table parts_jp(productno number,part_name varchar2(20));
I then populated the tables:
insert into products_jp values(1,’TV’);
insert into products_jp values(2,’CAR’);
insert into products_jp values( 3,’COMPUTER’);insert into parts_jp values(1,’ANTENNA’);
insert into parts_jp values(1,’CABLE’);
insert into parts_jp values(2,’BRAKES’);
insert into parts_jp values(2,’TYRES’);
insert into parts_jp values(2,’SPARK PLUGS’);
insert into parts_jp values(3,’MONITOR’);
insert into parts_jp values(3,’PRINTER’);
insert into parts_jp values(3,’KEYBOARD’);
insert into parts_jp values(3,’MOUSE’);select * from products_jp;
PRODUCTNO PRODUCT_NAME
——————– ——————–
1 TV
2 CAR
3 COMPUTERselect * from parts_jp;
PRODUCTNONO PART_NAME
——————— ——————–
1 ANTENNA
1 CABLE
2 BRAKES
2 TYRES
2 SPARK PLUGS
3 MONITOR
3 PRINTER
3 KEYBOARD
3 MOUSE9 rows selected.
As per the application design, the tables, products_jp and parts_jp,
are having one to many relation. An equi join query on these two tables will produce
the following output:
select a.productno,product_name,part_name
From products_jp a, parts_jp b
Where a.productno = b.productno;PRODUCTNO PRODUCT_NAME PART_NAME
——————– ————————- ——————–
1 TV ANTENNA
1 TV CABLE
2 CAR BRAKES
2 CAR TYRES
2 CAR SPARK PLUGS
3 COMPUTER MONITOR
3 COMPUTER PRINTER
3 COMPUTER KEYBOARD
3 COMPUTER MOUSE9 rows selected.
Now the business wants the above display to be presented in such a
manner, all the parts for a particular product should be displayed in a single
row. In the current RDBMS design, the products_jp and parts_jp tables have one
to many relation; as such the expected display of data is not possible.
What are the options, to achieve this display, as required by the
business:
01 Create a function to return all of the matching parts’
information from the second table in a single row. Call the frunction through
a select statement on the products table.
02 Rotate the parts table 90 degrees by creating another table (Current
columns in the parts table become rows and rows become columns in the
transposed table). Join the product information table with the transposed
parts table.
03 Create views or use analytic functions / inline views to
display the records from products and parts tables in a one to one
relation, as required.
04 Take the data from products table, do the manipulation through
shell scripting or Perl scripting and load the data into a new table. This
table is a transposed table of the source table.
05 Use a third party tool to achieve the requirement.
Implementation
01 Create a function to return all the matching parts’ information
from the second table in a single row. Call the frunction through a
select statement on the products table.
I created a function to display all the matching part names for
each product in a single row from PARTS_JP table.
create or replace function rotate_parts_func_jp(v_productno number)
return varchar2 as
v_part_name varchar2(1000):=”;
begin
for c1 in (select part_name from parts_jp
where productno = v_productno) loop
if (v_part_name IS NULL) then
v_part_name:=c1.part_name;
else
v_part_name:=v_part_name||’,’||c1.part_name;
end if;
end loop;
return v_part_name;
end;
/Function created.
Call the function from a select statement on products_jp, to display
the output as required.
select productno,product_name, rotate_parts_func_jp(productno) spare_parts
from products_jp;PRODUCTNO PRODUCT_NAME SPARE_PARTS
———- ———————————— ——————————————————–
1 TV ANTENNA,CABLE
2 CAR BRAKES,TYRES,SPARK PLUGS
3 COMPUTER MONITOR,PRINTER,KEYBOARD,MOUSE
However, if the table parts_jp is rotated 90 degrees, we can
straight away join the two tables.
02 Rotate the parts table 90 degrees and create a temporary table.
(Current columns in the parts table become rows and rows become columns in the
transposed table). Join the product information table with the transposed parts
table.
Create a spare_parts_jp table with the parts data transposed. (Rotating parts table by 90 degrees).
Create table spare_parts_jp(productno number,
Part_name1 varchar2(20),
Part_name2 varchar2(20),
Part_name3 varchar2(20),
Part_name4 varchar2(20));
Create a procedure to populate the SPARE_PARTS_JP table with data
from PARTS_JP.
declare
v_count number;
begin
for c1 in (select productno,part_name from parts_jp) loop
select count(*) into v_count from spare_parts_jp
where productno = c1.productno;
if (v_count = 0) then
insert into spare_parts_jp values(c1.productno,c1.part_name,null,null,null);
elsif (v_count > 0) then
for c2 in (select productno,part_name2,part_name3,part_name4
from spare_parts_jp where productno = c1.productno) loop
if (c2.part_name2 IS NULL) then
update spare_parts_jp set part_name2 = c1.part_name where productno = c1.productno;
elsif (c2.part_name3 IS NULL) then
update spare_parts_jp set part_name3 = c1.part_name where productno = c1.productno;
elsif(c2.part_name4 IS NULL) then
update spare_parts_jp set part_name4 = c1.part_name where productno = c1.productno;
end if;
end loop;
end if;
end loop;
end;
/PL/SQL procedure successfully completed.
Let us compare the data from the original parts table and
transposed parts table.
select * from parts_jp;PRODUCTNO PART_NAME
——————– ——————-
1 ANTENNA
1 CABLE
2 BRAKES
2 TYRES
2 SPARK PLUGS
3 MONITOR
3 PRINTER
3 KEYBOARD
3 MOUSE9 rows selected.
select * from spare_parts_jp;
PRODUCTNO PART_NAME1 PART_NAME2 PART_NAME3 PART_NAME4
——————– ——————– ——————— ——————– ———————-
1 ANTENNA CABLE
2 BRAKES TYRES SPARK PLUGS
3 MONITOR PRINTER KEYBOARD MOUSE
Let us join the rotated table SPARE_PARTS_JP with PRODUCTS_JP and
display the output.
select a.productno , product_name ,part_name1 ,part_name2 ,part_name3 ,part_name4
from products_jp a,spare_parts_jp b
where a.productno = b.productno;PRODUCTNO PRODUCT PART_NAME1 PART_NAME2 PART_NAME3 PART_NAME4
NAME
——————– ———— ——————– ——————– ———————- ————
1 TV ANTENNA CABLE
2 CAR BRAKES TYRES SPARK PLUGS
3 COMPUTER MONITOR PRINTER KEYBOARD MOUSE
For comparison, let us revisit our select query with function.
select productno,product_name, rotate_parts_func_jp (productno) spare_parts
from products_jp;PRODUCTNO PRODUCT_NAME SPARE_PARTS
——————– ————————- —————————————-
1 TV ANTENNA,CABLE
2 CAR BRAKES,TYRES,SPARK PLUGS
3 COMPUTER MONITOR,PRINTER,KEYBOARD,MOUSE
03 Create views / inline views or use analytic functions to
display the records from the products and parts tables in a one to one
relation, as required.
Generate the required output from the products table and parts
table using an analytic function.
select productno , product_name,
max(sys_connect_by_path(part_name, ‘ ‘ )) scbp
from (select a.productno ,product_name,part_name, row_number() over
(partition by a.productno, product_name order by part_name) rn from products_jp a, parts_jp b
where a.productno = b.productno ) start with rn = 1
connect by prior rn = rn-1 and prior productno = productno
group by productno, product_name
order by productno, product_name ;
(partition by a.productno, product_name order by part_name) rn from products_jp a, parts_jp b
*
ERROR at line 4:
ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as
part of column value
Researched and found the solution:
update parts_jp set part_name=’SPARKPLUGS’ where productno=2
and part_name=’SPARK PLUGS’;1 row updated.
SQL> commit;
Commit complete.
Execute the analytic function in the select statement.
select productno , product_name,
max(sys_connect_by_path(part_name, ‘ ‘ )) scbp
from (select a.productno ,product_name,part_name, row_number() over
(partition by a.productno, product_name order by part_name) rn from products_jp a, parts_jp b
where a.productno = b.productno ) start with rn = 1
connect by prior rn = rn-1 and prior productno = productno
group by productno, product_name
order by productno, product_name ;PRODUCTNO PRODUCT_NAME SCBP
———- ———— —————————————-
1 TV ANTENNA CABLE
2 CAR BRAKES SPARKPLUGS TYRES
3 COMPUTER KEYBOARD MONITOR MOUSE PRINTER
For
the convenience of creating a complex view, I modified the existing parts table
structure by adding a parts_no column and populated the table.
drop table parts_jp;
Create table parts_jp(productno number,part_no number,part_name varchar2(20));
insert into parts_jp values(1,1,’ANTENNA’);
insert into parts_jp values(1,2,’CABLE’);
insert into parts_jp values(2,1,’BRAKES’);
insert into parts_jp values(2,2,’TYRES’);
insert into parts_jp values(2,3,’SPARK PLUGS’);
insert into parts_jp values(3,1,’MONITOR’);
insert into parts_jp values(3,2,’PRINTER’);
insert into parts_jp values(3,3,’KEYBOARD’);
insert into parts_jp values(3,4,’MOUSE’);
Now I join the products table with the modified parts table to
produce the required output:
select productno, product_name,
(select part_name from parts_jp where productno = p.productno and part_no = 1) part_name1,
(select part_name from parts_jp where productno = p.productno and part_no = 2) part_name2,
(select part_name from parts_jp where productno = p.productno and part_no = 3) part_name3,
(select part_name from parts_jp where productno = p.productno and part_no = 4) part_name4
from products_jp p
/
PRODUCTNO PRODUCT_NAME PART_NAME1 PART_NAME2 PART_NAME3 PART_NAME4
———- ———— ———- ———- ———– ——————–
1 TV ANTENNA CABLE
2 CAR BRAKES TYRES SPARK PLUGS
3 COMPUTER MONITOR PRINTER KEYBOARD MOUSE
And for the solutions in 4 and 5, I leave it to readers’ choice
and imagination.
References
Tom Kyte’s blog on oracle.com
Steven M. Cohn’s Weblog on “Oracle PL/SQL Rotate/Pivot Query”
http://weblogs.asp.net/stevencohn/archive/2005/01/28/oracle-pl-sql-rotate-pivot-query.aspx
Oracle: transpose data table (rows into columns)
www.Code.techinterviews.com/oracle/oracle-transpose-data-table/67