Mappings with UNPIVOT component - ODI 12c

Unpivot will perform the exact opposite to Pivot transformation job. It will will transform columns into rows.

Assume you have data something like below in table.

Your goal is to convert sales into Quarter wise as separate rows.

Create a target table to Un Pivot the given source data.

  CREATE TABLE UN_PIVOT
   ( YEAR NUMBER,
QUARTER VARCHAR2(30),
SALES NUMBER
   )

Steps to follow in ODI.
1. Add source table into logical layer
2.Add UNPIVOT component
3.Open UNPIVOT component properties window and add three columns YEAR,QUARTER and SALES
and provide expression for YEAR column.

4.Open General and set the Row Locator as QUARTER

5. Open Unpivot Transformation and add the transformations to unpivot the data. Here we need to provide source column name whic needs to be populated as a row.

6.Finally map UNPIVOT component columns to Target table and set proper KMs.

Here is the ODI generated query


select
UNPIVOT.YEAR YEAR,
UNPIVOT.QUARTER QUARTER,
UNPIVOT.SALES SALES
from (
SELECT
  YEAR YEAR ,
  QUARTER ,
  SALES
FROM
  (
SELECT
  TRG_PIVOT_TEST.YEAR  YEAR ,
  TRG_PIVOT_TEST.Q1_SALES  Q1_SALES ,
  TRG_PIVOT_TEST.Q2_SALES  Q2_SALES ,
  TRG_PIVOT_TEST.Q3_SALES  Q3_SALES ,
  TRG_PIVOT_TEST.Q4_SALES  Q4_SALES  
FROM
  TRG_DW.TRG_PIVOT_TEST TRG_PIVOT_TEST  
  ) UNPIVOT INCLUDE NULLS (SALES FOR QUARTER IN (Q1_SALES AS 'Q1' , Q2_SALES AS 'Q2' , Q3_SALES AS 'Q3' , Q4_SALES AS 'Q4'))  
  ) UNPIVOT
where (1=1)