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.