Row into columns

[skp]

Hi please help me on the below requirement.....

My source is like
CustNo CustName
1 rajesh,naga,prabhu,jil,ray,chullet..........n

Output I need like
CustNo CustName
1 rajesh
1 naga
1 prabhu
1 jil
1 ray
1 chullet
. .
. .
. .
. .
1 n

How to achieve this in DataStage PX 7.5?

[ray.wurlod]

This is a pivot. Use a Pivot stage.

You may also need to use a Column Import stage to break the comma-delimited string into separate fields.

[skp]

Ray,

but in the i/p column there is no limit of the values. In this case what can i do.... please help...

[prasson_ibm]

Hi All,

This can be easily done in Datastage 8.5.

Step 1:- Read the source as it is in single column.

Step2:-

Define two stage veriables .

Code: Select all

Svar1=Field(incol," ",1)
Svar2=Field(incol," ",2)

Define Loop Condition as:-

Code: Select all

AITERATION=1 Or RemainingRecord=""

Loop Derivation:-

Code: Select all

InitRecord=If @ITERATION = 1Then Svar2 Else RemainingRecord
Rcd=Field(InitRecrod, ",",1)
RcdLength=Len(RcdTyp)
RemainingRecord=InitRecrod [RcdLength+1,9999]

Step3:- Create Two Columns in Output tab of transformer:-

Col1=Svar1
Col2=RcdTyp

I hope this will work.

[rupeshg]

Try this....

Code: Select all

.                                                            row generator 2 	
.                                                                     |
.                                                                     |
.                                                                     |
.                                                                     |
.                                                                     V
row generator 1 ------> transformer 1 --------> join --------> transformer 2 ----------> peek

row generator 1: one row with one column name "text", varchar datatype, value: "1 sam,alex,george,danny,mark"

transformer 1: 3 columns
col1: Field(DSLink11.text, " ",1)
col2: Field(DSLink11.text, " ",2)
dummy:0 key col

row generator 2: 2 columns generate number of records=10 (maximum that can come from source, in your case 'n')
num: datatype integer generate value: initial value=1, increment=1, limit=1000000 (maximum that can come from source, in your case 'n')
dummy: datatype integer generate value: initial value=0, increment=0, limit=0

join: key dummy
join type: inner
output 3 columns
col1: from source
col2: from source
num: from lookup

transformer 2: stage variable - sv33: Field(DSLink21.col2, ",", DSLink21.num)
output cols:
col1: from source
col2: sv33 derived from stage variable
output constraint: Len( sv33)>0

Run the job and check the output:

Peek_22,0: col2:sam col1:1
Peek_22,0: col2:alex col1:1
Peek_22,0: col2:george col1:1
Peek_22,0: col2:danny col1:1
Peek_22,0: col2:mark col1:1

======================================

If you can write a small shell script you can save lot of time for yourself.
Hint: use sed or tr command



Enjoy!

[rupeshg]

Code: Select all

                                         row generator 2
                                                  |
                                                  |
                                                  |
                                                  |
                                                  |
                                                  V
row generator 1 ------> transformer 1 --------> join --------> transformer 2 ----------> peek

row generator join..

[ray.wurlod]

but in the i/p column there is no limit of the values. In this case what can i do.... please help...

Define sufficient columns. Default unpopulated columns to NULL and filter these downstream of the Pivot stage.