Pentaho Transpose column to rows

I have this table in Pentaho.

YYYYMM  Value   Counts

201701  InSTOCK   10
201702  InSTOCK   5
201703  InSTOCK   2
201704  InSTOCK   4
201702  newstock  4
201703  newstock  8
201704  newstock  8
201701  reserve   3
201702  reserve   3

I would like to have my data like this

 YYYYMM   InSTOCK   newstock  reserve
 201701    10                   3
 201702    5          4         3
 201703    2          8
 201704    4          8

Is there a way to unpivot like this with Pentaho or perhaps a SQL query?

Answer

Yes, you could use SQL or Pentaho. With Pentaho you use the step Row denormaliser.

To use the Row denormaliser, you need to previously sort your rows by the columns you use to group your unpivot operation. In the operator, you define the group columns, the key column to perform the unpivot, and the new columns:

• Target fieldname is the name of the new column
• Value fieldname is the name of the incoming column you use to fill this Target fieldname
• Key value is the value that the Key field (column) you selected above to perform the unpivot. In your example Key value and Target fieldname have the same values, but you could define a different column name for your values.