How i can Do this

bollinenik · Post by **bollinenik** » Fri Nov 03, 2006 2:59 pm

Hi,

I have input like
FV A 0001 6999
FV A 8001 9099
FV A 9101 9299
FV A 9301 9999
GU A 15*
HL A 0001 6999
HL A 8001 9099
HL A 9101 9299

Required OutPut is
Fv A0001-A6999,A8001-A9099,A9101-A9299,A9301-A9999
GU A15*-A
HL A0001-A6999,A8001-A9099,A9101-A9299

In Oracle pluging Stage I did append Then I got required output but that is target level appending if it's reched max length then it's rejecting so, The same appending i need to do same at source leval This means in DataStage pls any one can help me
Thanks in advance

ray.wurlod · Post by **ray.wurlod** » Fri Nov 03, 2006 3:37 pm

Search the forum for "vertical pivot". There are several strategies available, depending on particular requirements (for example whether there is a known or arbitrary maximum number of rows to be pivoted). Therefore, without knowing the circumstances more fully, no-one is going to suggest "an" answer, as it may not be apposite.

vinodn · Post by **vinodn** » Sat Nov 04, 2006 6:37 am

Thx Ray
Following are my circumstences
parallel job is reading data from SAP and and loading into the Oracle So I need to do all that in DS, I am using stag variables and all but that loop is not repeatimg The scenario is like this
There are four fields from source like A,B,C,D Logic is
A BC-BD,BC-BD like that it should populate concatinate values the loop should continue up to value should change in A

Thx in Advance

ray.wurlod · Post by **ray.wurlod** » Sat Nov 04, 2006 2:35 pm

I understand completely what you are trying to do, except for the specific circumstances (such as whether there is a known maximum number of columns that will be generated). The technique is called "vertical pivot". How you accomplish it depends on circumstances such as that mentioned above. There is no "loop" needed - what is needed, generally, is that your input stream is sorted on the pivot key. You accumulate columns until a change in the pivot key occurs.