.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Pivoting Insert - Oracle

Posted By:Deepika Haridas       Posted Date: October 27, 2010    Points: 50    Category: DataBase    URL: http://www.dotnetspark.com  

This article describes about using Pivoting Insert in Oracle
 

Pivoting is an operation in which you need to build a transformation such that each record from any input stream, such as, a nonrelational database table, must be converted into multiple records for a more relational database table environment.

.Suppose you receive a set of sales records from a nonrelational database table, SALES_SOURCE_DATA in the following format:
EMPLOYEE_ID,WEEK_ID,SALES_MON,SALES_TUE,SALES_WED, SALES_THUR,SALES_FRI
.You would want to store these records in the SALES_INFO table in a more typical relational format:
EMPLOYEE_ID, WEEK, SALES
.Using a pivoting INSERT, convert the set of sales records from the nonrelational database table to relational format.


INSERT ALL
INTO sales_info VALUES (employee_id,week_id,sales_MON)
INTO sales_info VALUES (employee_id,week_id,sales_TUE)
INTO sales_info VALUES (employee_id,week_id,sales_WED)
INTO sales_info VALUES (employee_id,week_id,sales_THUR)
INTO sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
sales_WED, sales_THUR,sales_FRI
FROM sales_source_data;
5 rows created.


In the example in the slide, the sales data is received from the nonrelational database table SALES_SOURCE_DATA, which is the details of the sales performed by a sales representative on each day of a week, for a week with a particular week ID.


SQL> DESC SALES_SOURCE_DATA

Name Null? Type
EMPLOYEE_ID NUMBER(6)
WEEK_ID NUMBER(2)
SALES_MON NUMBER(8,2)
SALES_TUE NUMBER(8,2)
SALES_WED NUMBER(8,2)
SALES_THUR NUMBER(8,2)
SALES_FRI NUMBER(8,2)


SQL> SELECT * FROM SALES_SOURCE_DATA;

EMPLOYEE_ID WEEK_ID SALES_MON SALES_TUE SALES_WED SALES_THUR SALES_FRI
176 6 2000 3000 4000 5000 6000

SQL> DESC SALES_INFO

Name Null? Type
EMPLOYEE_ID NUMBER(6)
WEEK NUMBER(2)
SALES NUMBER(8,2)

SQL> SELECT * FROM sales_info;
EMPLOYEE_ID WEEK SALES
176 6 2000
176 6 3000
176 6 4000
176 6 5000
176 6 6000



Observe in the preceding example that using a pivoting INSERT, one row from the SALES_SOURCE_DATA table is converted into five records for the relational table, SALES_INFO.

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend