Mappings with Pivot component - ODI 12c

Pivot component will be used to transform the rows into columns.There is Pivot component available in ODI 12c .Lets see how it can be implemented.

Assume there is a table with Year, Quarter and sales amount for each Quarter.

And your goals is to load the Sales data for each Quarter as separate columns like below.

Create a target table to meet the goal and import into ODI models.

CREATE TABLE TRG_PIVOT_TEST (

year NUMBER,

q1_sales NUMBER,

q2_sales NUMBER,

q3_sales NUMBER,

q4_sales NUMBER

); 


Create a mapping with PIVOT component.

1.Add source table SRC_PIVOT_TEST into Logical layer
2.Add PIVOT component
3.Drag YEAR column into PIVOT component from source table,
4.Click on PIVOT component and open Properties window to configure Pivot.
5.Add QUARTER column to Row Locator
6.Add Row Locator values which are possible values (Q1,Q2,Q3,Q4) from QUARTER column to transform the data into columns.

7.Open Attributes to provide Expression/Matching rows for target columns.
For Year column choose Year in expression and for other pivotal columns choose SALES in Expression and the respective matching expressions Q1,Q2 etc  that we created under Row Locator values.

8.In General mention SUM as Aggregate function to sum the sales for each Year and Quarter

9.Finally add the target table TRG_PIVOT_TEST and map the columns from PIVOT component to target table.

Here is the query ODI generated.


select
PIVOT_.YEAR YEAR,
PIVOT_.Q1_SALES Q1_SALES,
PIVOT_.Q2_SALES Q2_SALES,
PIVOT_.Q3_SALES Q3_SALES,
PIVOT_.Q4_SALES Q4_SALES
from (
SELECT
  YEAR  YEAR ,
  Q1_SALES  Q1_SALES ,
  Q2_SALES  Q2_SALES ,
  Q3_SALES  Q3_SALES ,
  Q4_SALES  Q4_SALES
FROM
  (
SELECT
  SRC_PIVOT_TEST.YEAR  YEAR ,
  SRC_PIVOT_TEST.SALES  SALES ,
  SRC_PIVOT_TEST.QUARTER  QUARTER
FROM
  SCOTT.SRC_PIVOT_TEST SRC_PIVOT_TEST
  )  
  PIVOT  
  (
    SUM(SALES)
    for QUARTER in
    (
              'Q1'  AS Q1_SALES ,   'Q2'  AS Q2_SALES ,   'Q3'  AS Q3_SALES ,   'Q4'  AS Q4_SALES
    )
  )
  ) PIVOT_
where (1=1)