vertial & horizontal pivoting with variable number of co

ashish.singh · Post by **ashish.singh** » Fri Feb 20, 2009 3:17 am

Need help to do vertical and horizontal pivoting at same time and problem is that FACT columns may vary every time.

Input file -

MRKT|PROD|PRD|FACT1|FACT2|FACT3|FACT4
M1|P1|PR1|F11|F12|F13|F14
M1|P1|PR2|F21|F22|F23|F24
M1|P1|PR3|F31|F32|F33|F34
M2|P2|PR1|F41|F42|F43|F44
M2|P2|PR2|F51|F53|F54|F55
M2|P2|PR3|F61|F64|F65|F66

out put file -

MRKT|PROD|FACT|PR1|PR2|PR3
M1|P1|FACT1|F11|F21|F31
M1|P1|FACT2|F12|F22|F32
M1|P1|FACT3|F13|F23|F33
M1|P1|FACT4|F14|F24|F34
M2|P2|FACT1|F41|F51|F61
M2|P2|FACT2|F42|F52|F62
M2|P2|FACT3|F43|F53|F63
M2|P2|FACT4|F44|F54|F64

Please advice how to chieve this in datastage

Kirtikumar · Post by **Kirtikumar** » Fri Feb 20, 2009 3:31 am

Ashis,

For one type of pivoting there is stage provided by DataStage as pivot. Try that. For the other you have to use transformer and then aggregation.

If you search you will get many post which discuss this.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Feb 20, 2009 4:23 am

Need more information.

Why is the input to appearing in the fashion? That too when it is almost ready to be loaded into fact table !!

ashish.singh · Post by **ashish.singh** » Fri Feb 20, 2009 6:13 am

Yes this can be achieved but that is feasible when there will be fixed number of columns . In this case we have variable number of columns.

ashish.singh · Post by **ashish.singh** » Mon Feb 23, 2009 6:50 am

We need to develp a job so that we can create two different type of reports in xl file. For one key will be Market,Product ,Period and values will be fact and for one key will be Market,Product and fact and value will be a period.

But we have variable number of columns for period and fact . Means data may be like this as well.

MRKT|PROD|PRD|FACT1|FACT2|FACT3|FACT4
M1|P1|PR1|F11|F12|F13|F14
M1|P1|PR2|F21|F22|F23|F24
M2|P2|PR1|F41|F42|F43|F44
M2|P2|PR2|F51|F53|F54|F55
M2|P2|PR3|F61|F64|F65|F66
M3|P3|PR3|F31|F32||

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Feb 23, 2009 7:03 am

If I understand correctly, are you saying that there can be variable number of columns ??

In that case, how do you intend to store in a db? The structure will be variable.

Also, you may be better-off using features in xls to change (pivot) the result.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Feb 23, 2009 7:03 am

If I understand correctly, are you saying that there can be variable number of columns ??

In that case, how do you intend to store in a db? The structure will be variable.

Also, you may be better-off using features in xls to change (pivot) the result.