topic Re: How to unpivot a table? in Talend Studio

How to unpivot a table?

_AnonymousUser — Tue, 04 Jun 2013 09:35:03 GMT

Hello together,
I have some questions regarding the topic to unpivot a table in TOS-DI. I have a table like that:
| ID | Column1 | Column2 | Column3 | ... | ColumnN |
| 1 | ....... | ....... | ....... | ... | ....... |
| 2 | ....... | ....... | ....... | ... | ....... |
| ........................................................................ |
I would like to get this as an output:
| ID | Key | Value |
| 1 | Column1 | ..... |
| 1 | Column2 | ..... |
..........
| 1 | ColumnN | ..... |
| 2 | Column1 | ..... |
| 2 | ..... | ..... |
| 2 | ColumnN | ..... |
..........
I have found a component named tUnPivotRow which seems to do this task. But I cannot get it to run in Talend 5.0.1, 5.3.0 or 4.2.2. I have set the ID column as a row key. In the schema of the component on the left side are the columns of the first table above. On the right side is the schema of the second table (three columns). All columns are of type String.
When I am running the task, I am getting a bunch of errors scattered over the whole Talend job. The first lines are "status cannot be resolved to a variable". So I think that the component is not suitable for my versions of TOS.
Or is there something I am doing wrong? Is it possible to get this task done with other components?

Re: How to unpivot a table?

Anonymous — Tue, 04 Jun 2013 09:59:48 GMT

Hi,
Perhaps the custom component tTurnRow will satisfy your need
Please see the related forum https://community.talend.com/t5/Design-and-Development/resolved-tTurnRow-not-showing-on-pallet-in-talend-5-3-0/td-p/109840 for details.
Best regards
Sabrina

Re: How to unpivot a table?

_AnonymousUser — Tue, 04 Jun 2013 10:34:14 GMT

Hmm, thanks for your input, but I am afraid that is not what I want to do with my table. tTurnRow is turning the table 90° to the left and is mirroring it horizontally.
But I want one of the columns (the first one with the ID) in the source table to be treated specially (it only gets duplicated in the result table), the other columns in the source table to be treated as keys in the result table, and the values in these columns in the source table to be treated as values in the result table.
It's not the same, because with tTurnRow with a higher source table you would get a wider result table. But in my case the width of the result table would be always exactly three columns, because I want an ID-column, a key-column and a value-column.

Re: How to unpivot a table?

_AnonymousUser — Tue, 04 Jun 2013 10:56:57 GMT

I got it done! 🙂 I managed to use a single tSplitRow for this task.
The schemas in the tSplitRow are the same as mentioned in my first post. When you click on the "+" under "columns mapping" on the basic properties of tSplitRow you should see the three columns of the output schema - in my case "ID", "key", "value".
You then hit on "+" as many times you have columns in your source table minus 1. In the three fields you put the following:
"ID" -> always inputRow.ID (replace "inputRow" with your source-link-name, for example "out14")
"key" -> always a static string with the name of one of the columns on the left side, for example "Column2"
"value" -> "inputRow.Column2" (replace "inputRow" with your source-link-name, for example "out14", and "Column2" with the name of your column)
Do that for each column in the source table as already mentioned, and you get the result at least I want. 🙂

Re: How to unpivot a table?

Anonymous — Tue, 04 Jun 2013 11:10:25 GMT

Hi,
It is a bizarre solution, thanks for sharing your experience with us.
Best regards
Sabrina

Re: How to unpivot a table?

Anonymous — Wed, 25 Jun 2014 08:08:32 GMT

Can someone please help me in this regard
I am having the same problem as mentioned above with number of columns where i cannt mention it as a Static String.so i canot use tsplitrow component as i have many columns in my table/file..

I have some questions regarding the topic to unpivot a table in TOS-DI. I have a table like that:
| ID | Column1 | Column2 | Column3 | ... | ColumnN |
| 1 | ....... | ....... | ....... | ... | ....... |
| 2 | ....... | ....... | ....... | ... | ....... |
| ........................................................................ |
I would like to get this as an output:
| ID | Key | Value |
| 1 | Column1 | ..... |
| 1 | Column2 | ..... |
..........
| 1 | ColumnN | ..... |
| 2 | Column1 | ..... |
| 2 | ..... | ..... |
| 2 | ColumnN | ..... |

can you please help me with any easy solution?

Re: How to unpivot a table?

Anonymous — Wed, 25 Jun 2014 08:44:41 GMT

small hint
JSON file can give you this key value pair
Thanks
Vaibhav