If you have only 2 pairs of input columns (A, B), then this can be done with a “column group unpivoting”, followed by a Pivot node:
- use a Column Filter to separate columns A and B from each other
- rename columns so they share the names
- concatenate - these 3 steps effectively transform your input into the long format
- Pivot using ID as groups, type as pivot and sum of amount as aggregation - this takes care of a priori unknown types
- Join with original table to get all desired columns
- Missing Value to replace missing integers with 0
Produces the desired output. If you have an unknown number of column pairs, let me know.
dynamic column aggregation.knwf (48.7 KB)