How to transpose col values into col names

shalini11 · Post by **shalini11** » Fri Nov 13, 2009 1:18 am

Hi All,

My requirement is like this.

The input is :

Col1 Col2 Col3
Site1 Attr1 1
Site1 Attr2 2
Site1 Attr3 3

The output should be like:

Col1 Attr1 Attr2 Attr3
Site1 1 2 3

The number of values for Col2 in input dataset might change. So it should be dynamic

Your response would be highly appreciated.

Thanks,
Shalini

ShaneMuir · Post by **ShaneMuir** » Fri Nov 13, 2009 4:15 am

It can probably be done quite easily depending on what your target is?

Firstly you are looking at a vertical pivot to move the columns to rows.
A bit of tweaking post pivot and you should be able to isolate your column headers. If you are outputting to a seq file then that would be all you need (set to not include column names, and output your derived column names as the first row of the file).

Generally to perform a vertical pivot dynamically you would have to at least know the maximum number of columns. I am sure a search of vertical pivot will help you in your endeavours.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Nov 13, 2009 4:20 am

What is your target ?

How do you intend to cope with changing columns in the metadata ?

shalini11 · Post by **shalini11** » Sun Nov 15, 2009 10:14 pm

Thanks for your inputs.

My target is a DB2 table.

Can you pls elaborate on how can i set the derived colums as row in sequential file.

Thanks,
Shalini