Hello,
How to I change multiple rows into 1 column having all the values of multiple rows into 1 column separated by comma.
DATA IN PRESENT FORMAT
CD_P CD_B CD_S ACT MYYYY SLS_IDS
2U2Z 2V001 YA 38 12009 92
2U2Z 2V001 YA 38 12009 93
2U2Z 2V001 YA 38 12009 99
2U2Z 2V001 YA 38 12009 102
2UKK 2V099 MN 40 12009 91
9NCTS 76GV CT 55 12009 99
9NCTS 76GV CT 55 12009 102
DESIRED DATA FORMAT
CD_P CD_B CD_S ACT MYYYY SLS_IDS
2U2Z 2V001 YA 38 12009 92,93,99,102
2UKK 2V099 MN 40 12009 91
9NCTS 76GV CT 55 12009 99,102
So in above example, I want to take all SLS_ID for a group of
CD_P , CD_B, CD_S, ACT, MYYYY
and then concatenate them into 1 column.
Multiple Rows into 1 Column
Moderators: chulett, rschirm, roy
Hi,
You can do it in transformer. Inside the transformer, input link sort and partitioned on key. Define two stage variable, count (number) and IDS varchar. Append ID in IDS based on key and create output link with these two additional field. Next stage is remove duplicate with SAME partitioning method but sorted on count DESC and take the first row.
You can do it in transformer. Inside the transformer, input link sort and partitioned on key. Define two stage variable, count (number) and IDS varchar. Append ID in IDS based on key and create output link with these two additional field. Next stage is remove duplicate with SAME partitioning method but sorted on count DESC and take the first row.
Hi,
You can do it in transformer. Inside the transformer, input link sort and partitioned on key. Define two stage variable, count (number) and IDS varchar. Append ID in IDS based on key and create output link with these two additional field. Next stage is remove duplicate with SAME partitioning method but sorted on count DESC and take the first row.
You can do it in transformer. Inside the transformer, input link sort and partitioned on key. Define two stage variable, count (number) and IDS varchar. Append ID in IDS based on key and create output link with these two additional field. Next stage is remove duplicate with SAME partitioning method but sorted on count DESC and take the first row.