Concatenation of rows by using JMP menues or JSL

Question

How can text in a column "A" in different rows be concatenated, if the grouping value in another column "B" is equal and if the text in column "A" in the 2 rows are different?:

B       C          A

A1     Mix 1       Salt

A1     Mix 1       Sugar

A1     Mix 2       Salt

A1     Mix 2       Sugar

I want to output to be two rows:

B          C        A_combined

A1      Mix 1    Salt, Sugar

A1      Mix 2    Salt, Sugar

In practice, I have more columns than column B and C to be grouped by. ChatGPT says, that I can use Table Summary and under statistics choose concatenate, but that option is not available.

I have JMP 18.1.1

Jed_Campbell · Answer

If I'm understanding correctly, one way to do this is to use Tables > Transpose, then the Cols > Utilities > Combine Columns to do this, with a few extra columns created/deleted along the way. I've attached a sample workflow and data table that goes through this step by step.

test.jmp

Concat Rows.jmpflow

hogi · Answer

SQL Query:

Names Default To Here(1);

dt = New Table( "test",
	Add Rows( 4 ),
	New Column( "A",
		Character,
		Set Values( {"A1", "A1", "A1", "A1"} )
	),
	New Column( "B",
		Character,
		Set Values( {"Mix 1", "Mix 1", "Mix 2", "Mix 2"} )
	),
	New Column( "C",
		Character,
		Set Values( {"Salt", "Sugar", "Salt", "Sugar"} )
	)
);
Query(
	dt,
	"SELECT A, B, GROUP_CONCAT(C, ', ') as A_combined FROM 'test'
    group by A, B"
);

hogi · Answer

A) use a SQL Query

B)  vote here:
:folded_hands: Summary and Tabulate: add aggregation option for Character columns