How to transpose this table? (please see details)

[Ryanz3653]

Hi Alteryx Community,

I have a dataset as below example:

BEFORE:

ID	DATE1	DATE2	MATERIAL1	MATERIAL2	UNIT1	UNIT2	COMMENT1	COMMENT2
1	2020-1-1	2020-1-3	AA	BB	2	4	MMM	XXX
2	2020-6-10	2020-6-15	FF	EE	3	2	NNN	CCC
3	2023-10-5	2024-7-12	WW	QQ	2	3	BBB	VVV

AFTER:

ID	DATE	MATERIAL	UNIT	COMMENT
1	2020-1-1	AA	2	MMM
1	2020-1-3	BB	4	XXX
2	2020-6-10	FF	3	NNN
2	2020-6-15	EE	2	CCC
3	2023-10-5	WW	2	BBB
3	2024-7-12	QQ	3	VVV

Thank you for helping

[alexnajm]

Use two Select tools - one to select the ID and the “1” fields and another to select the ID and the “2” fields. Then union the two stream together!

[Qiu · Accepted solution]

@Ryanz3653 
I will go with the combination of Transpose and Cross Tab since it will be a bit dynamic.
I assume the column names contains the "number" as postfix.