Welcome.
It most certainly is a pivot, actually a "vertical pivot" of rows to columns. However, you are correct in that the actual Pivot
stage only supports the other direction - columns to rows or a horizontal pivot - until the upcoming 8.5 release, at least from what I seem to remember I've read. So, in the meantime, you get to do it yourself!
If you've been searching the forums, you'll find most advice on vertical pivoting says to search the forum. Advice
is out there, it's just the wheat is buried amongst a ton of chaff.
What comes to mind is transformer stage variable work along with an Aggregator. You need to do basic 'group change detection' so you know when your DM_OPERATION_ID value changes and you can do that all in stage variables or with a preceding Sort stage with a Key Change column added. If you go the latter route, make sure you tell it "Don't sort, already sorted".
In the transformer, accumulate REASON_CODE values in a single field and go ahead and output the single field. The Aggregator comes into play to capture the 'Last' record from each group of DM_OPERATION_ID values. So, output looking something like this:
Code: Select all
4 158
4 158,156
4 158,156,865
4 158,156,865,901
4 158,156,865,901,483 <- this row is passed by the Aggregator
5 184
5 184,163
-etc-
Use the key change column being 1 or 'true' to know when to reset the accumlating stage variable back to just the initial value. Every subsequent row where the key change column is 0 or 'false' you concatenate a comma and the current value onto the accumulating value.
Hope that helps and can be integrated into your current design. I'm sure there are other ways to skin this cat but that's the typical approach to this.
