Performing a complete transposition of rows into columns requires a combination of operations.

Example

The input file presents coverage types as columns, so there is one row per policy number and two columns for every coverage type, since premium and commission is stated for each coverage type.

The desired output table presents coverage codes as rows with a premium and commission value for each.

Method

1. Map the Coverage header names to coverage codes, appended with ‘PREM’ or ‘COMM’.
2. Swing down all the Coverage columns in one operation.

1. Swing Down columns ‘CORP_PROP PREM’ ‘CORP_PROP COMM’, etc. Output column name ‘COVERAGE_ID.
2. Value column: ‘Value’
3. Premium and Commission values are now all in one column, so split out into separate columns using a pair of IF statements in Calculate. Optionally, an additional pair of IF statements can replace blanks with zero in the Premium and commission columns.

4. Using Calculate, clean up the coverage names to remove the ‘PREM’ or ‘COMM’ suffix (and whitespace).

LEFT(COVERAGE_ID, LENGTH(COVERAGE_ID)-5)

5. Aggregate back up to remove the duplicated rows.