DataStage is only capable of outputting a fixed number of columns. Your specification would need to specify the maximum possible value for n (call it max(n)), and your design would have to output max(n) columns. Your specification would also need to specify the default value to be output in the case where n < max(n). Also, is there a "key" column changes in which can trigger output of a set of n rows? And are the input data sorted by this value?
I have seen three approaches used to accomplish this kind of thing. Because they were on separate sites I am unable to provide any comparative throughput or other measures, even if I werent bound by non-disclosure agreements. One design used a Transformer stage with max(n) output links. In this case
max(n) was small. The values from m rows are accumulated in memory (common or stage variables) and output when a change in key value is detected.
Another design loads multi-valued columns in a UniVerse file/table, then extracts using the "normalize on" capability. The third design (courtesy of Jim Tsimis) performs a similar accumulation to the first, but generates a single "column" of output, replacing the dynamic array delimiters in the storage dynamic array with end-of-lines, writing the result into a text file then reading it from there with max(n) columns as the metadata. This one flies along!
Another thought: is it possible to transpose your data then use a pivot stage to perform a horizontal pivot?
> ----------
> From: Walker, Phil (Forests Manukau)[SMTP:
Phil.Walker@chh.co.nz]
> Reply To:
informix-datastage@oliver.com
> Sent: Thursday, 23 November 2000 15:16
> To:
informix-datastage@oliver.com
> Subject: Vertical pivoting
>
> A challenge for someone? (Ray
)
>
> We need to construct a job whereby we vertically pivot n * m input
> rows into m rows with n columns.
> The n for each m may not be the same! That is m1 may have 10 columns
> whereas
> m2 may have 1 and m3 may have 4.
>
> I know I could do this by writing some preprocess code, but can we, or
> has someone done it via designer gui and some smart logic in a
> transform or something similar? If so, how performant was it?
>
> Regards,
>
> Phil Walker
> DataStage Consultant
> Project Clarity
> Carter Holt Harvey Forests
> DDI: 96094
> MOD: 025 835 197
> EMAIL:
phil.walker@chh.co.nz
>