Concatenate two fields from different rows

[harinder1301]

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

[atcodedog05 · Accepted solution]

Hi @harinder1301 

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 : )

[harinder1301]

Perfect. Thank you very much!

[atcodedog05]

Happy to help : ) @harinder1301 

Cheers and have a nice day!