how to "unpivot" my table properly

[spotfire newbie_2018]

I have the following table (dummy example) in Spotfire and would like to "transform/unpivot" the table.

Please see attachment for the example in Excel format or below - .

header row: subject item value

data row1: 1 car1 100

data row2: 1 car2200

data row3: 1 house1 1000

data row4: 2 car1 100

data row5: 2 car2 350

data row6: 2 house2280

 

I want to "unpivot" (not in the "traditional sense") the table into

header row represents subject IDs

header row: (item) 1 2 3 4 ...

data row1: car1 100 100 ...

data row2: car2 200 350 ...

data row3: house1 1000 - ...

data row4: house2 - 280 ...

 

It appears that the available function of unpivotdoes not transform the original table into the desired table.

ps. the real challenge is the original table has 38,115,596 rows.

[Shashank Gurnalkar]

You can use a pivot function as shown below. I am getting the expected result with the sample data set you have given.