To Convert Rows to Columns

The post explains how to convert rows into columns

The data will be in the below format after doing the operations in SQ
CASE, NAME, COL_NAME
BCO, BCO Name, X_ATTRIB_01
BCO, Comment, X_ATTRIB_02
CRN_APPS_CODE_MAPPING, APPS Code, X_ATTRIB_03
CRN_APPS_CODE_MAPPING Endorsement, X_ATTRIB_04
AKCOV, Billing Code, X_ATTRIB_06
AKCOV, Class Of Car, X_ATTRIB_05
AKCOV, Comments, X_ATTRIB_02


The Target data must be in the below format
BCNAME,X_ATTRIB_01,X_ATTRIB_02,X_ATTRIB_03,X_ATTRIB_04,X_ATTRIB_05
BCO, BCO Name, Comment
CRN_APPS_CODE_MAPPING, , ,APPS Code, Endorsement
AKCOV, ,Comments, , , Class Of Car, Billing Code

To Achieve this we follow the below flow and conditions
SQ--->EXP--->AGG--->TGT

Create the output ports based on the columns you require. In my condition I require 32 columns so I created 32 output ports
The condition is based on the Name and SRCHSPEC columns so based on these I calculated in the expression transformation

O1 (OUT) ---> IIF (NOT ISNULL (SRCHSPEC) AND COL_NAME = 'X_ATTRIB_01', NAME)
The same is calculated to all the 32 columns

All the 32 output ports and the SRCHSPEC are passed to the aggregator transformation
group by SRCHSPEC

Create 32 output ports and the following calculations is used in all output ports base on the Input column

Out1 (OUT)----> MAX(DECODE(NOT ISNULL(O1),1,O1))

Pass SRCHSPEC and all 32 output ports to the target