2

I have table like below

ID Name
1  a
1  b
1  c
2  d
2  e
3  f

I would like to get result as

ID Name
1  a,b,c
2  d,e
3  f

I don't want to use any XMLPATH or coalesce functions. Just in simple SQL query I need to get the expected result.

Cœur
  • 37,241
  • 25
  • 195
  • 267
user1912935
  • 361
  • 4
  • 13
  • 34

2 Answers2

2

Since you are on Oracle 10g version, you cannot use LISTAGG. It was introduced in 11g.

And please DON'T use WM_CONCAT as it is an undocumented feature, and has been removed from the latest release. See Why does the wm_concat not work here?

For 10g, you have following string aggregation techniques:

  1. ROW_NUMBER() and SYS_CONNECT_BY_PATH
  2. User-defined function STRAGG as demonstrated by Tom Kyte here https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
  3. COLLECT function in PL/SQL

Following is a pure SQL method using ROW_NUMBER() and SYS_CONNECT_BY_PATH functions available since 9i:

SQL> column emp_list format a50
SQL> SELECT deptno,
  2         LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
  3         KEEP (DENSE_RANK LAST ORDER BY cur),',') AS emp_list
  4  FROM   (SELECT deptno,
  5                 ename,
  6                 ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS cur,
  7                 ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
  8          FROM   emp)
  9  GROUP BY deptno
 10  CONNECT BY prev = PRIOR cur AND deptno = PRIOR deptno
 11  START WITH cur = 1;

    DEPTNO EMP_LIST
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>
Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
1

In Oracle 10 you can use the inofficial function WM_CONCAT for this. In later versions you'd use LISTAGG.

select id, wm_concat(name)
from mytable
group by id;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • 1
    Don't use `WM_CONCAT` since it is undocumented, and moreover it has been removed from the latest release. See http://stackoverflow.com/a/28758117/3989608 – Lalit Kumar B Apr 29 '15 at 08:23
  • @Lalit Kumar B: Undocumented is not the problem, the syntax is easy: `WM_CONCAT([DISTINCT] column_name)`. The problem is, as I did mention, that it is inofficial. So it may not be avaliable in a certain installation. The only alternative I know of is to write a custom aggregation function, but well, you can as well use WM_CONCAT and only write a custom WM_CONCAT when you get in a situation where the function is lacking. – Thorsten Kettner Apr 29 '15 at 08:57
  • @Lalit Kumar B: Ah, I just read your answer. So there *are* alternatives :-) – Thorsten Kettner Apr 29 '15 at 08:59
  • I would allow the developers to use it only in test environment as a one time activity. Never in production. You know the reply from the Oracle Support `Oh!, wm_concat... We don't support it` – Lalit Kumar B Apr 29 '15 at 09:02