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:
.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.
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,
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
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
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.
No response found. Be the first to respond this post
You must Sign In To post reply
|Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here|