My Oracle Support Banner

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 to [Release 10gR3 to 11gR1]
Information in this document applies to any platform.


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.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.