Manipulating a data structure in Pig/Hive

Question

I'm not really sure how to phrase this question, so please redirect me if there is a better place for this question.

Right now I have a data structure, more or less organized like this:

I want my data to look like this:

Sorry for the images, apparently I can't use markdown to make these!

I realize my question is similar to this one, but ideally I would like to be able to do this in Pig, but knowing how to do it in Hive, R, Python, or Excel/LibreCalc would be useful/interesting too.

I'm not even sure what this kind of data manipulation is called, so directing me to some sort of general wiki page would be helpful.

Answer 1

@vkp got me started in the right direction, but I had to add a few tweaks to get it working on Hive:

CREATE TABLE myDatabase.newTable STORED AS TEXTFILE AS 
SELECT item, year, 'jan' AS Month, jan AS Value FROM myDatabase.myTable UNION ALL
SELECT item, year, 'feb' AS Month, feb AS Value FROM myDatabase.myTable UNION ALL
SELECT item, year, 'mar' AS Month, mar AS Value FROM myDatabase.myTable;

Still interested in a solution that works on Pig.

Answer 2

I am not sure if this will work in Hive. I know it is pretty similar to SQL. Give it a try.

select item, year,
'Jan' as Month,
Jan as value
from yourtable
UNION
select item, year,
'Feb' as Month,
Feb as value
from yourtable
UNION
select item, year,
'Mar' as Month,
Mar as value
from yourtable