I’m trying to combine multiple records, where there will be 1 to 3 occurrences of the records to be combined, into one record. For the records to be combined there are some fields that will be the same, and some that are different. What I want to do is pivot these records into a single record.
I haven’t been able figure out how to do this.
The input profile is database profile, and profile output will be XML but shown as CVS about for brevity.
Source example data:
field1, field2, field3, field4, field5, field6, field7
AAA, BBB, CCC, ID1, EEE1, FFF1, GGG1
AAA, BBB, CCC, ID2, HHH2, III2, JJJ2
AAA, BBB, CCC, ID3, KKK3, LLL3, MMM3
field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14, fiels15
AAA, BBB, CCC, ID1, EEE1, FFF1, GGG1, ID2, HHH2, III2, JJJ2, ID3, KKK3, LLL3, MMM3
If there are less than three records in the set to be combined, the pivoted data will be null in the output record.