Assign column name of a table as a attribute name of a new table.

Five Stars

Assign column name of a table as a attribute name of a new table.

I have table like

 

id    Make           Model      Size

1     Nike             sp2           8  

2     Puma          poer          9

3     Adidas         v3            10


Expected output:


To a new table:


id      attribute_name      attribute_value

1        Make                        Nike

1        Model                       sp2

1        Size                          8

2        Make                        Puma

2        Model                       poer

2        Size                          9

3        Make                        Adidas

3        Model                       v3

3        Size                          10

Sixteen Stars

Re: Assign column name of a table as a attribute name of a new table.

This intrigued me, so I did it. This is not the most basic way of doing this (you could just hard code the column names instead of working them out dynamically), but I fancied seeing how difficult it would be. The job I built looks like this....

 

solution1.png

 

You can see the data as you requested. The tFixedFlowInput component just provides precisely the data you gave as an example (your input data) with the same column names. The important code here is carried out in the tJavaFlex and the tMap.

 

tJavaFlex

In this component I provide 2 output columns; column_name and column_number. The component returns the number of columns (minus the id column) in rows. So in your example it returns 3 rows. Each row has the column number and the column name. The code to do this is below...

 

Start Code

//The "row1" is an object of class row1Struct. This is standard
//in Talend jobs. We create an empty version here.
row1Struct rs = new row1Struct();

//We use the empty row to call it's toString() method which give us 
//the column names and values.
//This is edited to remove extraneous characters
String columns = rs.toString().substring(rs.toString().indexOf('[')+1);
columns = columns.substring(0,columns.length()-1);

//We now split this String by comma to get an array of column names and 
//and values in the format "name=value"
String[] column_names = columns.split(",");

//Now we open a loop to iterate for each column except the first
//column (id)
for(int i = 1; i< column_names.length; i++){

Main Code

//Remove the value section of the array token String
//and pass the column name to the output column
row2.column_name = column_names[i].substring(0,column_names[i].indexOf('='));

//Set the column_number ("i" will start at 0)
row2.column_number = i+1;

End Code

//We close the loop 
}

tMap

This component takes the rows returned by the tJavaFlex and uses those to multiply the input Main row. It uses the data to create the output structure required. Below is am image of the structure of the tMap....

solution2.png

There are two tMap variables called "column_name" and "column_value". The expressions for those are...

 

column_name (not actually required, could just map the row2.column_number to the output...but its in the screenshot :-) )

row2.column_name 

 

column_value

row2.column_number == 2 ? row1.make : row2.column_number == 3 ? row1.model : row2.column_number == 4 ? row1.size+"" : null 

 

Column_value checks the column_number and uses the basic inline IF shown above to pull out the column value required. 

 

With this structure, each row will be multiplied by the number of columns (minus the id column) and each output row will have the id, the column_name and column_value for each column.

Five Stars

Re: Assign column name of a table as a attribute name of a new table.

While executing the following errors occurs... How can I solve this issue??

 

Screenshots are attached with this...

Five Stars

Re: Assign column name of a table as a attribute name of a new table.

Found a simple way to resolve this using component tSplitRow. Just hard code the necessary columns..