Oracle Database: Rotating Tables

July 19, 2007

by JP Vijaykumar

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              COMPUTER

select * from parts_jp;

PRODUCTNONO                 PART_NAME
---------------------       --------------------
          1                 ANTENNA
          1                 CABLE
          2                 BRAKES
          2                 TYRES
          2                 SPARK PLUGS
          3                 MONITOR
          3                 PRINTER
          3                 KEYBOARD
          3                 MOUSE

9 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		MOUSE

9 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		MOUSE

9 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

http://tkyte.blogspot.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