How to Rotate (Pivot Or Transpose) a Table Using ODI 10g or 11g?
(Doc ID 423694.1)
Last updated on JANUARY 24, 2023
Applies to:
Oracle Data Integrator - Version 10.1.3.2.0 to 11.1.1.9.99 [Release 10gR3 to 11gR1]Information in this document applies to any platform.
Goal
How can you rotate (pivot or transpose) a Table using Oracle Data Integrator (ODI) 10g or 11g?
In other words, how can ODI be used to store in a new table information that is originally in rows and put it into columns, and vice versa?
Pivot queries involve transposing rows into columns (pivot) or columns into rows (unpivot) to generate results in crosstab format.
Pivoting is a key technique used in data warehouses, where you transform multiple rows of input into fewer and generally wider rows in the data warehouse.
Oracle has recently added the PIVOT and UNPIVOT keywords to the SQL language SELECT statement in the Oracle Database 11gR2.
These are extensions to the SELECT statement will allow ODI 11g to generate the same SQL syntax to alow the PIVOT and UNPIVOT SQL functions with ODI without the additon of a separate integration server.
Pivoting is a key technique used in data warehouses, where you transform multiple rows of input into fewer and generally wider rows in the data warehouse.
Oracle has recently added the PIVOT and UNPIVOT keywords to the SQL language SELECT statement in the Oracle Database 11gR2.
These are extensions to the SELECT statement will allow ODI 11g to generate the same SQL syntax to alow the PIVOT and UNPIVOT SQL functions with ODI without the additon of a separate integration server.
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |