0

Possible Duplicate:
comma-separated list as a result of select statement in Oracle

I Have fields like this in a SQL table:

ELEMENT_CODE  DIAG_CODE
------------------------
FTD           1234
FTD           1243
FTD           6384
FTD           9938
FLA           9292
FLA           3654
FLA           1122

I need records like this in a report in oracle database: 

ELEMENT_CODE  DIAG_CODE
------------------------
FTD           1234-1243-6374-9938
FLA           9292-3654-1122
Community
  • 1
  • 1
Pranu
  • 1
  • 1
  • 2
    [Welcome to StackOverflow](http://stackoverflow.com/faq)! What *kind* of table? HTML? SQL? Card? What have you tried so far? Help us to help you! Please edit your question; as it stands it cannot be answered and should be closed. – johnsyweb Mar 23 '11 at 01:17
  • Its an SQL table, I'm working on oracle 91 database to pull this info and produce it on a report. – Pranu Mar 23 '11 at 01:21
  • @Pranu. Thanks. ...and what have you tried so far? – johnsyweb Mar 23 '11 at 01:26
  • I'm new to sql, I think i must use group by,set operation, but unable to get an idea how can i avoid repetition of the first name in first colum. – Pranu Mar 23 '11 at 01:35
  • Giving as much detail as possible is important, because, for example, I know how to do this for SQL Server (using XML PATH) but have no idea how to do it in oracle. – SWeko Mar 23 '11 at 01:37
  • @MPelletier and @Johnsyweb thanks very much for your support and immediate response. – Pranu Mar 23 '11 at 01:50

1 Answers1

3

For Oracle 11g release 2:

SELECT ELEMENT_CODE, LISTAGG(DIAG_CODE, '-') WITHIN GROUP (ORDER BY DIAG_CODE) AS "DIAG_CODES"
FROM SomeTable
GROUP BY ELEMENT_CODE;

Pre Oracle 11g release2 would be:

SELECT ELEMENT_CODE, WM_CONCAT(DIAG_CODE, '-')
FROM SomeTable
GROUP BY ELEMENT_CODE;

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

MPelletier
  • 16,256
  • 15
  • 86
  • 137