Request Info
Overview Aggregate Cross-Calculate Custom Transforms Formats/Keys Lookups Match/Join Pivot/Unpivot Re-Map/Reformat Scrub/Cleanse Select/Filter Sort/Merge Substrings Type-Convert


Normalized row and column data representations may require compaction to a de-normalized layout by rotating rows into columns. This is a common requirement in statistical cross-tab reports. To pivot efficiently and prevent duplicate rows, the data should be pre-sorted. In other tools, that step is separate, and slow in volume.

Normalizing de-normalized content, or unpivoting presents other issues. For example, rows can have unexpected values or be missing entirely when the column values were null or zero. Sorting after is separate.

SQL pivot and unpivot commands, where available, are not always simple or portable across databases. ETL tools can require complex aggregate or union transforms (with "ports" and "expressions"), or specific pivot/unpivot transform editors with many properties to configure.

SQL CrossTab scripts are also clunky, and of no use for remapping fields and records in a flat file. XSL "for-each" transforms to flatten XML files are even more complex. Then there is the question of how all these methods perform in volume.


The SortCL program in the IRI CoSort package or IRI Voracity platform transposes rows and columns through intuitive field reformatting in a simple job wizard or 4GL job scrip, and single I/O pass.

To Pivot

Define the layout of the source rows in the input section of the script. In the pre-action layout, reformat the pivot row into the new column if it does not contain a 0 or null value. Sort on the aggregation key. In the output, collapse the pivot key columns with summary statements to produce unique values, and format.

To Unpivot

Define the source columns on input. Reformat in the pre-action layout, and sort on the pivot key. The output section can further transform and refine the target layout as needed.

Request More Information

* indicates a required field.
IRI does NOT share your information.