how to transpose dynamic columns array into rows

[shivshankar]

Input 

Id	email	office_0_location	office_0_phone	office_1_location	office_1_phone
1	abc@email.com	address1	123456	address2	456652

need the output in this format.

Id	email	location	phone
1	abc@email.com	address1	123456
1	abc@email.com	address2	456652

[dsandmann]

Hi -

What you're looking for can be accomplished using the arrange tool (assuming there is a set number of locations/phones)

[shivshankar]

Thanks, this works fine with static columns.

But office_[index]_location and office_[index]_phone are dynamic columns, it may have more columns based on the input data.

Could you please help me to resolve this issue.

office_0_location

office_0_phone

office_1_location

office_1_phone

office_2_location

office_2_phone

[shivshankar]

Thanks, this works fine with static columns.

But office_[index]_location and office_[index]_phone are dynamic columns, it may have more columns based on the input data.

Could you please help me to resolve this issue.

office_0_location

office_0_phone

office_1_location

office_1_phone

office_2_location

office_2_phone

[dsandmann · Accepted solution]

Hi- 

This update should help address that. Just does a transpose, splits the names, and then joins back on record position before checking to make sure that at least one of the location/phone columns has a value. If you send multiple workers through but they have differing numbers of locations/phones it will present nulls for the ones with less.

[shivshankar]

Thanks, this works fine with static columns.

But office_[index]_location and office_[index]_phone are dynamic columns, it may have more columns based on the input data.

Could you please help me to resolve this issue.

office_0_location

office_0_phone

office_1_location

office_1_phone

office_2_location

office_2_phone