Hide+ BI & Analytics
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.
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.
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.