Converting Coumns To Row
Moderators: chulett, rschirm, roy
Converting Coumns To Row
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
-
- Premium Member
- Posts: 68
- Joined: Sat Feb 05, 2005 4:59 am
- Location: India
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.
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.
Re: Converting Coumns To Row
Thanxs for the help.... I have got the solution
Re: Converting Coumns To Row
Hi Adarsh,
Can you pls elaborate the idea.I mean how to append?
Can you pls elaborate the idea.I mean how to append?
-
- Premium Member
- Posts: 68
- Joined: Sat Feb 05, 2005 4:59 am
- Location: India
Re: Converting Coumns To Row
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
where ',' wud be used as the delimiter to split the values using a row-splitter
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!
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!
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 68
- Joined: Sat Feb 05, 2005 4:59 am
- Location: India