Pivot Data Using Informatica

You can use the Normalizer Transformation to generate rows out of columns. But to pivot values in rows into columns you would have to use Aggregator Transformation.  Basically, pivot can be done in two ways

Pivot columns in a single row into multiple rows
 

Source

AddressID	Name	Address1	Address2
1	Rachel Geller	address1	address2
2	Joey Smith	address1	address2

Target

AddressID	Name	Address
1	Rachel Geller	address1
1	Rachel Geller	address2
2	Joey Smith	address1
2	Joey Smith	address2

In the above scenario, the column data is converted to rows. To normalize the data, we use normalizer transformation.  Go to Normalizer Transformation, to see, how to implement the above scenario.

Pivot data from multiple rows into columns in a single row
 

Source

AddressID	Name	Address
1	Rachel Geller	address1
1	Rachel Geller	address2
2	Joey Smith	address1
2	Joey Smith	address2

Target

AddressID	Name	Address1	Address2
1	Rachel Geller	address1	address2
2	Joey Smith	address1	address2

In this scenario, the row data is converted to column. This can achieved by using an aggregator transformation.

Source -> AggregatorTransformation -> Target

In the aggregator Transformation, group by AddressID and Name.

Create two Output Ports o_Address1 and o_Address2.

Set the Expression as

o_Address1: First(Address)

o_Address2: Last(Address)

*First or last functions will pick first or last of the incoming rows if there are duplicates.

If the source has variable number of rows like

CUSTOMER_ID, CUST_NAME, COMMENT

1,Rachel Geller,aaa

1,Rachel Geller,bbb

2,Joey Smith,ccc

2,Joey Smith,ddd

2,Joey Smith,eee

3,Ivar Smith,fff

I would like to Pivot, example, four comments from my data.

CUSTOMER_ID	CUST_NAME	COMMENT1	COMMENT2	COMMENT3	COMMENT4
1	Rachel Geller	aaa	bbb	Null	Null
2	Joey Smith	ccc	ddd	eee	Null
3	Ivar Smith	Fff	Null	Null	Null

In this scenario, we use expression and aggregator transformation.

Source -> Expression -> Aggregator -> Target

In the expression create 3 variable and one output port:

v_Check_New_Cust_ID: IIF(CUSTOMER_ID=v_last_CUST_ID,'Y','N')

v_Count: IIF(v_Check_New_CUST_ID='N',1,v_Count+1)

v_last_CUST_ID: CUSTOMER_ID

o_count: v_count

In the aggregator group by CUSTOMER_ID and create 4 output address ports:

o_COMMENT1: MAX(COMMENT,o_count=1)

o_COMMENT2: MAX(COMMENT,o_count=2)

o_COMMENT3: MAX(COMMENT,o_count=3)

o_COMMENT4: MAX(COMMENT,o_count=4)

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.

Informatica Powercenter Express - Normalizer Transformation

Normalizer transformation is an active transformation that transforms one source row into multiple target rows. The transformation can pass source data from one source row to multiple targets to reduce target file size and to decrease data redundancy. When you define the Normalizer transformation, you configure an input row hierarchy that describes the source data structure.

Generated Column ID

The Normalizer transformation returns a Generated Column ID  output port for each instance of a multiple-occurring field. For example, if a field occurs four times in a source record, the Developer tool returns a value of 1, 2, 3, or 4 in the generated column ID port based on which instance of the multiple-occurring data occurs in the row.

Input Hierarchy Definition

When we create a Normalizer transformation, we define an input hierarchy that describes records and fields in the source.The Developer tool creates the transformation input ports based on our definition.

Occurs column in the Normalizer view identifies the number of instances of a field or record in a source row.

Level column in the Normalizer view indicates where a field or record appears in the input hierarchy.

Output Groups and Ports

The output groups and ports are defined in the Overview view of the Normalizer transformation. You can create the output ports after you define the transformation input hierarchy. The Developer tool generates the first level output groups by default. The Developer tool does not generate an output group for any record. You must manually configure how you want to return the multiple-occurring groups of fields.

Advanced properties for normalizer transformation

Automatically generate first-level output groups Automatically generates the output groups for level 1 fields.

Tracing Level Amount of detail that appears in the log for this transformation.

Scenario

Normalize multiple-occurring quarterly sales data 

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.