Convert Column values into column names

[rmmr]

Hi,

My requirement is like below.

Input:

EmpId Year sal
1 1980 1000
1 1981 2000
2 1980 3000
2 1981 4000

My Output is like below

Output:

EmpId 1980 1981

1 1000 2000
2 3000 4000

Please assist me on this.

Thanks in advance.

[gateleys]

You can't do that.

[chulett]

Well... perhaps. Forget about them being 'column names' and try writing them as the first data record.

[mcs_dineshm]

Its possible.. First use a filter stage using two where class namely year=1981 and year =1980. In the filter output1 change year as year1 and in filter output2 change year as year2, same for sal also change as sal1 & sal2. Now merge the two filtered output using a merge stage with empid as key. Then in the output column enter the colums as empid,1980,1981. Now in the output mapping map as below,

Empid------>Empid
sal1--------->1980
sal2---------->1981

Try this out.. thanks

[mcs_dineshm]

Its possible.. First use a filter stage using two where class namely year=1981 and year =1980. In the filter output1 change year as year1 and in filter output2 change year as year2, same for sal also change as sal1 & sal2. Now merge the two filtered output using a merge stage with empid as key. Then in the output column enter the colums as empid,1980,1981. Now in the output mapping map as below,

Empid------>Empid
sal1--------->1980
sal2---------->1981

Try this out.. thanks

[dsusr]

Input ---> Transformer ---> Two links from Transformer i.e. Link1 and Link2

Link1 ( only the year column ) -----> Remdup (on the years ) ----- Transformer

Transformer writes a single record which contains all the distinct years concatenated with any delimter ( assume it to be pipe) and Text 'EmpId' also added at the start and separated by the same delimiter. This link will have the following output:-

Column1
EmpID|1980|1981|.....any number of years

Link1 ----> Remdup ----> Transformer ----- Outlink1

I am just assuming the name of this output link as Outlink1 which is going to have only one row and one column.


Link2 from the transformer should go to a sort stage which just sort the date on the basis of EmpID, year and The output from sort would go to the transformer which then writes the record to the output only when the key is changing, transformer is going to concatenate the EmpId with salary for the same EmpId. This link should have the following output:-

Column1
1|1000|2000|.....
2|3000|4000|.....

Link2 -----> Sort Stage ----- Transformer ---- Outlink2

Just assume the name of this output link as Outlink2. This link is going to have one column only but the number of rows would be equal to the number of Distinct Emp Ids.

Funnel the output of both the links with first read from Outlink1.


Assumptions in this approach:-- Every emp-id is having an entry for each year, if you dont have that scenario then will try another approach to solve this problem.