trouble with pivot

[datastagenewbie]

Hi I am trying to use pivot and am not getting the desired output, i searched the forum but none helped
Here is my input
Col1, Col2
--------------
a,1
a,2
a,3
a,4
b,1
b,5
b,6

And the output I want is

Col
-----
a
1
2
3
4
b
1
5
6


By using pivot I am getting
a
1
a
2
a
3
a
4
b
1
b
5
b
6


Please help!!!

[chulett]

That's not really a pivot. You could "pre-process" the data into a form that the pivot stage would like however, I would assume. Or perhaps do this all with stage variables in the transformer.

I'm short on time (and brains atm) so will leave the gory details to others...

[datastagenewbie]

I used Sorter , and then in the transformer I used this expression.
If keyChange=1 then Col1 : char(10): Col2 Else Col2

Got me the desired results. :-)

[chulett]

Very nice.

[agpt]

can u pls explain the syntax:

If keyChange=1 then Col1 : char(10): Col2 Else Col2

what it does exactly?

Sorry to ask this but I am a new learner so....

[anbu]

a,1
a,2
a,3
a,4
b,1
b,5
b,6

If you set the option Create Key Change column in sort to true then sort creates a column keyChange and output of sort is

a,1,1
a,2,0
a,3,0
a,4,0
b,1,1
b,5,0
b,6,0

If keyChange is one then add a newline char between Col1 and Col2 else just Col2

Code: Select all

If keyChange=1 then Col1 : char(10): Col2 Else Col2 

[agpt]

Thanks