Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Possible Duplicate:
how to query multiple row data in single column in oracle?

my table structure is given bellow

deptid  ename
------  ------
10  CLARK,KING,MILLER
20  ADAMS,FORD,JONES,SCOTT,SMITH

but i want the output sould be like

deptid  ename
----------------
10      CLARK,
        KING,
        MILLER
------------------
20      ADAMS,
        FORD,
        JONES,
        SCOTT,
        SMITH

please tell me how to do it in sql

share|improve this question
2  
Why is that your table structure? Having comma delimited multiple values in a single column violates first normal form. Also your desired results appear to be something that should be done in the presentation layer anyway. – Martin Smith Apr 9 '12 at 10:47
this is not the tabele structurewhat you are showing us. it is (perhaps) the output of a select statement. you also did not show the query you execute to get the output. @Martin Smith wrote that it depends on the tool you use to display the output of the query. i suppose you use sqlplus to produce this output but you should tell us what tool you use. – miracle173 Apr 9 '12 at 11:06
Thanks for your answar.this clears my doubt regards sabyasachi – sabyasachi Apr 9 '12 at 11:18

marked as duplicate by Martin Smith, Justin Cave, Leigh Riffel, JNK♦ Jun 3 '12 at 11:26

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

up vote 0 down vote

select deptid, replace(ename, ',', ',\n') from table;

Try to use this query and it will gives you your expected result.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.