Converting Coumns To Row

[neeraj]

Hi Friends,

I Have one requirement like: I have a 2 columns in my Source table as follows:

First_Name last_Name

Rahul Verma
Rahul Mahajan
Mohit Khanna
Mohit Aggarwal
Mohit Shah

And my Final Output table has three columns and after transformation, result must be as follow:

First Name last_name1 last_name2 last_name_3

Rahul verma Mahajan
Mohit Khanna Aggarwal Shah


Can Anyone suggest me the solution?

Regards
Neeraj Mahajan

[kumar_s]

Hi Neeraj,
Make a search on key word 'vertical pivot'.
you can lot of information regarding this.

regards
kumar

[Sainath.Srinivasan]

I will suggest you to search the forum before posting a question.

Did you try using Pivot or hash files?

[chulett]

Well... searching for 'vertical pivot' will lead one to the Pivot stage which won't solve their problem. Yes, it does turn 'columns into rows' but that not what the OP needs based on the posted example.

This is a 'horizontal pivot' actually and you technically need 'rows to columns'. Search on that instead and you'll see a number of discussions on using sorted data and stage variables to check for changes in repeating groups. You'll need to do something like keep track of which last name 'occurance' you are working with inside each group of first names and then write out a record when the first name changes.

Of course, recognizing when you are at the end is always problematical and requires adding a recognizable dummy record to your source. Or there's the possibility of sorting descending so you key from the first record in each group, not the last.

There are other approaches. For example - Leverage a hashed file with your output structure and use the first name as the key. On a miss, write a record to the lookup with the key and last_name_1 populated, one each hit check for the first empty last name field and work your way down populating them one by one. When you are done, load from the hashed file. Make sure you don't pre-load the lookup to memory.

[rumu]

Hi all,
Horizontal pivot pivots horizontal data, that is, columns within a single row into many rows.But in this case we need reverse condition i.e mapping vertical data in many rows into a single row which can be done using vertical pivot which is not supported.I am trying out this example using hashfile and keeping name field as key and using stage variables since it retains data for previous row only,but it is working on ly with 1st ex ie-Rahul Verma Mahajan,in latter case since there is 3 titles it is getting updated with last 2 titles,pls suggests how to solve this problem.
Thnx in advance.

[adarsh shrinagesh]

Hi

All u need to do is sort the list based on the First name...for each row similar to the previous (on the First name ) keep appending the previous Last Name to the current one with a delimiter of any sort.

All u need to do then is to write to a hash file(key as first_name) - the last row with all the Last_Names appended in one field for a RAHUL or a MOHIT is written.

All u need then is a row splitter.

[neeraj]

Thanxs for the help.... I have got the solution

[rumu]

Hi Adarsh,
Can you pls elaborate the idea.I mean how to append?

[adarsh shrinagesh]

append? if thats ur question this is what i meant Link.currentvalue:',':ConcatenatedValuesOfPreviousRows

where ',' wud be used as the delimiter to split the values using a row-splitter

[chulett]

Appending like that would be a valid solution when you have an unknown number of values you are working with.

I was under the impression that Neeraj had a finite number of last name columns to work with - three to be exact. That's why I suggested the hashed file approach with one key field (the first name) and three data fields (the last names). It's the easiest to implement, doesn't require pre-sorting the data or any kind of row splitting afterwards.

Is that what you ended up doing, Neeraj? Or did you take some other approach? Please post your solution for future folks who come searching with a similar problem. Thanks!

[rumu]

Neeraj,
I have done this without using sort command,using stage variables to retain previous values and using hashfile with name field as key.
Did u come up with same solution or anything else,if ur approach is different please share it.

[adarsh shrinagesh]

Hi rumu

I'm not quite sure whether u've got a correct design without using a sorter...retaining previous values only makes sense when u have grouped similar rows together...if that is not maintained, the storing of previous values wud result in incorrect results.

[rumu]

Hi Adarsh,
Actually I have worked out with the sample input given by neeraj,so need to sort here,but in general yes, we need to sort the data and I have tried it using sort command in filter stage.