Exercise 9: How to Convert Column to Row

Exercise 9: How to Convert Column to Row and achieve target below?

Source
CITY,NAME
BNG,JOY
BNG,LABNITA
CHE,SRINIVAS
CHE,JOYDEEP
CHE,SHRUTHI
PUNE,PRIYA

Target
CITY,NAME
BNG,JOY,LABNITA
CHE,SRINIVAS,JOYDEEP,SHRUTHI
PUNE,PRIYA

Let's Chat: Though I have covered approx 2-3 scenarios that how to convert column to row. This scenario is too basic so, I am covering it in exercise.

Theory: Yes, you are thinking right, first we need to sort the data then we have to concatenate name until the next City arrived then we need to group records and select max(name) based upon city.

Practically

Step 1: Drag and Drop your source and target tables.

Step 2: 
Sorter
Sort the data based upon City.

CITY (Key Port)

Step 3:
Expression

CITY_COMPARE_FLAG (Variable Port)= IIF(CITY=OLD_CITY, 1, 0)

CITY_COMPARE (Variable Port) = IIF(CITY=OLD_CITY, OLD_CITY, CITY)

OLD_CITY (Variable Port) = CITY_COMPARE

NAME_CONCAT (Variable Port) = IIF(CITY_COMPARE_FLAG=1, OLD_CONCAT||','||NAME, NAME)

OLD_CONCAT (Variable Port) = NAME_CONCAT

OUT_NAME (Output Port) = NAME_CONCAT

Step 4
Aggregator
Connect CITY and OUT_NAME column from expression to Aggregator and create one more port

NAME (Output Port) = MAX(OUT_NAME)

Check GROUP port for CITY

Step 5
Connect NAME and CITY from Aggregator to Target.

BINGO!!!

 Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com