listagg--列转行函数

19c版本中listagg函数可以不匹配within group进行使用

with temp as(
  select 'China' nation ,'Guangzhou' city from dual union all
  select 'China' nation ,'Shanghai' city from dual union all
  select 'China' nation ,'Beijing' city from dual union all
  select 'USA' nation ,'New York' city from dual union all
  select 'USA' nation ,'Bostom' city from dual union all
  select 'Japan' nation ,'Tokyo' city from dual
)
select
       nation,
       listagg(city,',') city
from temp
group by nation; 
+--------+----------------------------+
| NATION | CITY                       |
+--------+----------------------------+
| China  | Guangzhou,Shanghai,Beijing |
| Japan  | Tokyo                      |
| USA    | New York,Bostom            |
+--------+----------------------------+

11G版本中使用listagg必须要使用within group来配合使用否则报错

ERROR at line 10:

ORA-30491: missing ORDER BY clause

SQL> select index_name,listagg(COLUMN_NAME,',') within group(order by COLUMN_POSITION) city from dba_ind_columns where table_name='T1' group by index_name;
INDEX_NAME                     CITY
------------------------------ ------------------------------------------------------------
IDX_T1_OBJECT_ID_OWNER         OBJECT_ID,OWNER
IDX_T1_OWNER                   OWNER