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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

my table structure is given bellow

deptid  ename
------  ------

but i want the output sould be like

deptid  ename
10      CLARK,
20      ADAMS,

please tell me how to do it in sql

share|improve this question

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

This question was marked as an exact duplicate of an existing question.

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

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.