Conver columns to rows in oracle

rprasanna28 · Post by **rprasanna28** » Tue Sep 19, 2006 5:01 am

I have a table with the following data in SQL

SNO NAMES

------ -----------------

1 prasanna

1 lakshmi

1 Ravikumar

2 raja

2 lakshmi

2 natrajan

3 deepika

3 bidri

3 G

And I need the output as following using SQL query

SNO FNAME MNAME LNAME

1 prasanna Lakshmi Ravikumar

2 Raja Lakshmi natrajan

3 Deepika bidri G

If any one of U find the solution please let me know.

Mike · Post by **Mike** » Tue Sep 19, 2006 6:46 am

It is not possible with the data you have provided. How do you identify which row corresponds to which name? How do you identify which row is the "first" row for a given SNO. In other words, you have no way to order your names. If you figure out some way to order your rows, then a search of the forum for vertical pivot should reveal some DataStage solutions.

Mike

meena · Post by **meena** » Tue Sep 19, 2006 9:25 am

Hi,
Check with Row Merger active plugin stage.Do a search on Row merger stage in the forum. And move this post to server from parallel.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 19, 2006 2:42 pm

Search for "vertical pivot" for this is what you are trying to accomplish.

kumar_s · Post by **kumar_s** » Tue Sep 19, 2006 7:15 pm

If the number of records for each group will be only 3, then its possible by using mod function and some corelated subquery. But it is more simple to accomplish from datastage.

DSguru2B · Post by **DSguru2B** » Tue Sep 19, 2006 10:35 pm

I did something like this a few months back. Check out thispost for more details.