How To Rotate (Pivot Or Transpose) A Table Using ODI 10g or 11g? (Doc ID 423694.1)

Last updated on FEBRUARY 08, 2017

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.

Solution

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 hundreds of Community platforms