Pivoting-unknown number of columns

dsnk · Post by **dsnk** » Thu Dec 19, 2013 11:14 pm

Hi
I have a requirement to process following:
I/P:
A1|val1;val2;val3.....;valn|C3
B1|val2;var3;var5;..........;varn|C3

O/P:
A1|Val1|C3
A1|val2|C3
...
...
A1|valn|C3
B1|val2|C3
..
..
..
B1|varn|C3

Hear the 2nd column has unknown number of values which has to splitted across for each of the value in column 1

Please help

RPhani · Post by **RPhani** » Fri Dec 20, 2013 12:38 am

Hi,

Use Transformer Looping concept.

----------------------------------------
R.Phani

ray.wurlod · Post by **ray.wurlod** » Fri Dec 20, 2013 11:59 am

Or use Pivot stage and discard the NULLs subsequently.

dsnk · Post by **dsnk** » Fri Dec 20, 2013 1:08 pm

Looping is not available in 8.1. I believe this is available from 8.5. Can you please suggest alternatives.

dsnk · Post by **dsnk** » Fri Dec 20, 2013 1:11 pm

The set of values in column-2 is dynamic. Some times it may ave 1 value and at times 50,100, so on.. Is it possible to use pivot for dynamic number of columns ? Please help steps if possible.

chulett · Post by **chulett** » Fri Dec 20, 2013 1:56 pm

The Pivot stage needs a fixed number of columns... as in the maximum you could possiblty get. Then as Ray notes you'd need to filter out the unused values (the NULLS) post-pivot. It works but you can end up creating a ton of records to filter out.

Without transformer looping you may need to do this outside of DataStage, something at the O/S level perhaps.