HOW TO CONVERT ROWS INTO THE COLUMNS IN DATASTAGE

HOW TO CONVERT ROWS INTO THE COLUMNS IN DATASTAGE
If we have some customers information with different address as below.

mult_add
e_id,e_name,e_add
10,john,melbourne
20,smith,canberra
10,john,sydney
30,rockey,perth
10,john,perth
20,smith,towand



If w like to get all multiple addresses of the customer into one single row from multiple rows. .

We can perform this using Sort Stage, Transformer Stage and Remove Duplicate Stage

Take Job Design as below


SeqFile----Sort-----Tx----R.D----D.S


Read and load the data in Seq.File

In Sort Stage Select key column and select Key change = True to generate group id's

In Transformer stage Create one Stage variable and select name as temporary

and Write expression for that as

If keychange=1 then s_add else temporary:",": s_add And click ok

Go to Remove dupilcates and select last in properties and select key column

to remove dupilicates ( You can select address column here )

That's it compile and run the job

You will get the required output.


One more way todo it:

CONVERT ROWS INTO COLUMNS USING SORTING AND TRANSFORMER STAGE

If you have Some Data like below to convert rows into the columns


xyz_comp
e_id,e_name,e_add
100,jam,chicago
200,sam,newyork
300,tom,washington
400,jam,indiana
500,sam,sanfransico
600,jam,dellas
700,tom,dellas


Take Job Design as

Seq.File----Sort-----Tx-----R.d-----D.s

Tx- Transformer stage

R.D- Remove Duplicates Stage

Here we are taking remove duplicate stage, inorder to remove duplicates after getting the output.


Read and Load the Data in Sequential file stage .

In Sort Stage Select Key column as e_name

and select key change column as True

In output Drag and Drop all the Columns

Go to Transformer stage Create two stage variables as Temp and Add

Map the key change to temp and

in add derivation write expression as

If temp=1 then e_add else add:',':e_add

Than create one column in output table as hist_add

Now Drag and Drop the Add(From Stage Varable ) to Hist_Add (Output Column )


That's it Click ok

In Remove Duplicate stage Select key column as e_add than

Select Duplicate to retain as last and click ok

Give File name to the target file

Compile and Run the Job.