Hi
I would like to concatenate the unique fields from this sample table as shown below:
rowid | col1 | col2 | col3 | col4 |
1 | 1 | test1 | abc1 | xyz1 |
1 | 2 | test1 | null | |
1 | 3 | null | null | |
1 | 4 | null | null | xyz1 |
2 | 1 | test11 | abc11 | xyz1 |
2 | 2 | test12 | abc12 | xyz1 |
2 | 3 | null | null | |
3 | 1 | test21 | abc21 | xyz21 |
3 | 2 | test22 | abc22 | xyz22 |
Required Output | ||||
rowid | col2 | col3 | col4 | |
1 | test1 | abc1 | xyz1 | |
2 | test11,test12 | abc11,abc12 | xyz1 | |
3 | test21,test22 | abc21,abc22 | xyz21,xyz22 |
I am using the summarize tool but I am not getting an option to concatenate the unique fields .Appreciate your help.
Regards
Harinder
Solved! Go to Solution.
Here is how you can do it.
Workflow:
1. Convert rows to columns
2. Filter null values
3. Groupby on rowid column name and value to get unique values
4. Using crosstab with aggregate mode as concat to convert it to the output table.
Hope this helps : )
Perfect. Thank you very much!
Happy to help : ) @harinder1301
Cheers and have a nice day!