Help me on this Scenario

[41ranjeet]

I have below src as my input
COL1 COL2
A 10
B 20
C 30
D 40
A 110
B 210
C 310
D 410
A 120
B 220
C 320
D 420

I want result like below in tgt

A B C D
10 20 30 40
110 210 310 410
120 220 320 420

Its an example of src data,in actual src for COL1 A,B,C and D are repeated for more then 100 times.

[chulett]

Looks like a vertical pivot to me of rows to columns. What have you tried?

[41ranjeet]

Sorry for the late reply Craig,Yes I did try with vertical result but I am not getting the expected result,from vertical pivot I am getting value like below.

Col1 Col2_val1 Col2_val2 Col2_val3.........
A 10 110 120 ..........
B 20 210 220 .........

also Pivot index has a restriction of Array size 1000.
I want Col1 as my column name and Col2 as value from source and after each 4 rows the value of source should again value for the 2nd row of the target.As described as an example in my post.

[battaliou]

You could build up the output string in a stage variable and release it with a constraint. e.g.

stagevar1 : if in.COL1 = 'A' then in.COL2 else stagevar1 : ',' : in.COL2

Set single varchar output column to stagevar1 and constrain in.COL1 = 'D'

Run in sequential mode.

[ppalka]

you can also define a loop in a transformer and use a SaveInputRecord function