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.