Articles in this section

Reshape transformations

  • Updated
Follow

Column-level operations that reshape the data layout — pivot, transpose, flatten nested structures, convert between rows and columns. Configure each one under Transformation → MAPPING → Complex Transformations.

Denormalize (pivot)

Pivot data from multiple rows into columns in a single row, grouped by one or more key columns.

Parameters:

  • Columns to Group By — comma-separated list. Required.
  • Columns to Include — comma-separated list of columns to keep in the output.
  • Columns to Exclude — comma-separated list to drop. Mutually exclusive with the include list.
  • Leading Column — the column that drives the row count per group. If Address is leading and there are 2 addresses but 4 phones for a group, phones are repeated to match 2 (not the full 4).
  • Max # of Columns — cap on how many times a denormalized column is repeated.

Example. Group by id:

Before
id,address,phone
1,main street1,412111111
1,anderson dr,412111112
2,home,
3,home,home phone
3,work,work phone
3,,mobile phone

After
id,address,address_2,address_3,phone,phone_2,phone_3
1,main street1,anderson dr,,412111111,412111112,
2,home,,,,,
3,home,work,,home phone,work phone,mobile phone

Configure: MAPPING → Complex Transformations → Denormalize dataset.

Denormalize key-value pairs

Group a dataset by specified fields and convert key-value pairs into columns and rows. The Key and Value columns are user-defined — you tell Etlworks which column holds the key name and which holds the value.

Parameters:

  • Columns to Group By — required. Group-by columns are automatically included in the output.
  • Key Column Name — required. The source column whose values become column names.
  • Value Column Name — required. The source column whose values become column values.
  • Columns to Include — extra columns to carry through.
  • All Columns — optional ordered list of every column in the final dataset.

Example. Group by id, Key = phone_type, Value = phone:

Before
id,phone_type,phone
1,cell,412111111
1,home,412111112
2,home,412111113
3,home,412111114
3,work,412111115
3,cell,412111116

After
id,cell,home,work
1,412111111,412111112,
2,,412111113,
3,412111116,412111114,412111115

Configure: MAPPING → Complex Transformations → Convert key-value pairs to columns and rows.

Normalize as key-value pairs

The inverse of denormalize — convert columns into key-value pair rows, where the key is the original column name and the value is its value.

Parameters:

  • Columns to Convert — comma-separated list of columns to convert. Other columns pass through unchanged.
  • Columns to Not Convert — the inverse: everything except these gets converted. Ignored if Columns to Convert is set.
  • Key Column Name — the output column for the key.
  • Value Column Name — the output column for the value.

Example. Columns to Convert = type, name, Key Column = key, Value Column = value:

Before
id,type,name
1,first,joe
2,last,jude
3,last,who

After
id,key,value
1,type,first
1,name,joe
2,type,last
2,name,jude
3,type,last
3,name,who

Configure: MAPPING → Complex Transformations → Convert columns to the key-value pairs.

Extract dimensions

Flatten a nested dataset by extracting a nested element into one row per occurrence, joined back to the parent row's columns. Available when the source is a file or an API endpoint.

Note: Disables streaming. The transformation runs last and needs the entire nested object in memory.

Parameters:

  • Extract Dimensions — comma-separated path to the nested element.
  • Columns to Include Together with Dimensions to Extract — parent-row columns to repeat on each extracted row.

Example. JSON source:

[{
  "firstName": "Duke", "lastName": "Java", "age": 18,
  "phoneNumbers": [
    {"type": "Mobile", "phoneNumber": "111-111-1111"},
    {"type": "Home",   "phoneNumber": "222-222-2222"}
  ]
}]

With Extract Dimensions = phoneNumber and Columns to Include = firstName, lastName, age:

firstName,lastName,age,phoneNumber_type,phoneNumber
Duke,Java,18,Mobile,111-111-1111
Duke,Java,18,Home,222-222-2222

Configure: MAPPING → Complex Transformations → Extract Dimensions.

Transpose

Swap rows and columns — original rows become columns, original columns become rows.

Original
first,last
joe,doe
hey,jude
the,who

Transposed
column_0,column_1,column_2
joe,hey,the
doe,jude,who

Configure: MAPPING → Complex Transformations → Transpose dataset.

Wrap columns

Reduce a wide dataset to the first N columns and wrap remaining values onto subsequent rows.

With Max # of Columns in a Row set to 1:

Before
first,last
joe,doe
hey,jude
the,who

After
first
joe
doe
hey
jude
the
who

Configure: MAPPING → Complex Transformations → Wrap columns.

Per-field mapping

Per-field mapping is a feature of the Mapping step itself rather than a separate transformation. Define field-by-field source-to-destination mappings (including type conversions, scripted expressions, and conditional mappings) directly in the mapping editor.

See the Mapping section for full coverage of mapping mechanics.

Related articles