Transposing Rows into columns and Joining Data in two tables

[Absolom]

Dear Community,

I am new to the KNIME Analytics platform and I am enjoying it…

I have a challenge creating a workflow described as below:
I am connected to a database and I have data about people stored separately in two tables - the first table stores the attribute meta data i.e. ID and Name of attribute while the other table store the values for those attributes. In both tables, data is stored as rows.

Table with attribute metadata

Table with attribute values
I wish to create a workflow which joins the two tables (have a common column id key) but the issue i have is transforming the rows in the first table as columns join to the second table which i should also have its rows transposed into columns.

In the resulting table, i wish to have the attribute names displayed as columns and attribute values as rows.

If there is someone out there familiar with such kind of workflow please guide me.

I have attached screen shots for more clarification.

Thank you.

Absolom

[ScottF]

Hi @Absolom -

Welcome to the forum!

Here’s an example that might help. I made a couple of small toy datasets that are similar to yours, and joined them together. I then used a Pivoting node to arrange the data. I used standard KNIME nodes for this, but the concept should be the same for the Database nodes.

JoinandPivotExample.knwf (10.5 KB)

Inputs:

Output:

[ipazin]

Hi there @Absolom,

have nothing to add to @ScottF solution only recommendation to use new database node instead of legacy. They offer more possibilities

Br,
Ivan

[Absolom]

Thank you @ipazin and @ScottF for your swift response and support. Your ideas have helped me in successfully modifying and executing the work flow.

Absolom