반응형
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 |
WITH t(type, name, code) AS( SELECT '과일', '사과', '0' FROM dual UNION ALL SELECT '과일', '레몬', '1' FROM dual UNION ALL SELECT '과일', '포도', '2' FROM dual UNION ALL SELECT '과일', '참외', '3' FROM dual UNION ALL SELECT '채소', '오이', '0' FROM dual UNION ALL SELECT '채소', '당근', '1' FROM dual UNION ALL SELECT '채소', '호박', '2' FROM dual ) SELECT type , SUBSTR(XMLAgg(XMLElement(x, ',', name) ORDER BY code).Extract('//text()'), 2) name_9i , wm_concat(name) name_10g , ListAgg(name, ',') WITHIN GROUP(ORDER BY code) name_11g FROM t GROUP BY type ORDER BY type ; |
<실행 결과>
| TYPE | NAME_AGG |
| 과일 | 사과,레몬,포도,참외 |
| 채소 | 오이,당근,호박 |
출처 : http://www.oracleclub.com/article/55512
