How to Get the Column Value in Newline in Oracle 10g

[Gurujothi D]

Hi Everyone,
Here my requirement is,
My Emp table have the following 3 columns,

Id , Name and programming

it has the following values,

Id Name Programming
-- ---- -----------
1 Raju Java,Plsql,sql
2 Kamal C,C++,Java


now I need the output in the following format,
Id Name Programming
-- ---- -----------
1 Raju Java
plsql
sql
2 kamal c
c++
Java.

Can anyone suggest me the code to get the above format?
Thank you in advance.


With regards,
gurujothi.D

[rski]

Actually from what you paste here it is hard to find out what output format do you want.

[Gurujothi D]

Dear rski,
Directly I edited but it is not a right format,eventhou gh i edited the format which i need it became like above, from where i have to edit and paste it here? please let me know?

[rski]

Use [code] tags.
Do you want something like that?

Id Name Programming 
-- ---- -----------
1  Raju  Java 
         plsql 
         sql 
2  kamal c 
         c++ 
         Java.

I mean two displayed rows but programming splitted into multiple lines?

[Gurujothi D]

Dear RSKI,
I need the exact same above output.How can I get that?

[rski]

Try this

select id, name, translate(wm_concat(programming),',',chr(13)) as programming from test group by id, name;

[Gurujothi D]

Dear RSKI,
Am getting the following error in toad,
ORA-00904: "WM_CONCAT" : invalid identifier

[rski]

This function is owned by WMSYS user. Maybe you have no provilege to use it.
Does this query return something

select * from all_objects where object_name ='WM_CONCAT'

[Gurujothi D]

Hi RSKI,
No rows returned.

[rski]

Looks like you have no privilege to use this function.
After googling I foun this forum

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

you should find solution here.

[Gurujothi D]

Hi RSKI,
Thank you so much for your passion.