Transpose Columns into Rows from a Resident Table

[Alessandro_TZ9]

Hi to everyone,

I need to transpose only some columns into rows from a resident table,

from this:

Supplier	Value A	Percentage A	Value B	Percentage B
Ford	100	1	150	1.5
Brown	150	2.5	300	3
McCallan	180	1.8	270	2.4

.. into this, creating a new associated table:

Supplier	Value	Percentage
Ford	100	1
Ford	150	1.5
Brown	150	2.5
Brown	300	3
McCallan	180	1.8
McCallan	270	2.4

What is the simpliest way to proceed?

Many thanks in advance

Alessandro

[SerhanKaraer]

Hello Alessandro,

You can do it as below:

Load Supplier, Subfield(Fields, '|', 1) as Value, Subfield(Fields, '|', 2) as Percentage;
LOAD Supplier, Subfield(Fields, ';') as Fields;
LOAD Supplier, "Value A" & '|' & "Percentage A" & ';' & "Value B" & '|' & "Percentage B" as Fields; 
LOAD * INLINE [
Supplier	Value A	Percentage A	Value B	Percentage B
Ford	100	1	150	1.5
Brown	150	2.5	300	3
McCallan	180	1.8	270	2.4
] (delimiter is '\t');

exit script;

 I hope it solves your issue.

[MarcoWedel · Accepted solution]

tabResult:
LOAD Supplier,
     [Value A] as Value,
     [Percentage A] as Percentage
Resident YourResidentTable;

Concatenate (tabResult)
LOAD Supplier,
     [Value B] as Value,
     [Percentage B] as Percentage
Resident YourResidentTable;

Drop Table YourResidentTable;