오라클 LISTAGG 사용법

오라클 LISTAGG 문법[11g부터 사용가능]

 

 

 

 

오라클 LISTAGG 예제1

SELECT LISTAGG(last_name, '; ')
         WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
       MIN(hire_date) "Earliest"
  FROM employees
  WHERE department_id = 30;

 

수행결과

Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

 

 

 

오라클 LISTAGG 예제2

SELECT TABLE_NAME,
       LISTAGG(COLUMN_NAME,',') WITHIN GROUP(ORDER BY COLUMN_ID)  
  FROM DBA_TAB_COLUMNS
  GROUP BY TABLE_NAME;

수행결과

TABLE_NAME                    |LISTAGG(COLUMN_NAME,',')WITHINGROUP(ORDERBYCOLUMN_ID)                                                                                               
------------------------------|----------------------------------------------------------------------
ACCESS$                       |D_OBJ#,ORDER#,COLUMNS,TYPES                                                        
ACCOUNT_MANAGERS              |ACCT_MGR,REGION,COUNTRY,PROVINCE,NUM_CUSTOMERS                                     
ADM_EXPFIL_ASET_FUNCTIONS     |OWNER,ATTRIBUTE_SET_NAME,UDF_NAME,OBJECT_OWNER,OBJECT_NAME,OBJECT_TYPE             
ADM_EXPFIL_ATTRIBUTES         |OWNER,ATTRIBUTE_SET_NAME,ATTRIBUTE,DATA_TYPE,ELEMENTARY,COMPLEX,STORED

 

 

 

오라클 LISTAGG 예제3

SELECT department_id "Dept", hire_date "Date", last_name "Name",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
         OVER (PARTITION BY department_id) as "Emp_list"
  FROM employees
  WHERE hire_date < '01-SEP-2003'
  ORDER BY "Dept", "Date", "Name";

수행결과

 Dept Date      Name            Emp_list
----- --------- --------------- ---------------------------------------------
   30 07-DEC-02 Raphaely        Raphaely; Khoo
   30 18-MAY-03 Khoo            Raphaely; Khoo
   40 07-JUN-02 Mavris          Mavris
   50 01-MAY-03 Kaufling        Kaufling; Ladwig
   50 14-JUL-03 Ladwig          Kaufling; Ladwig
   70 07-JUN-02 Baer            Baer
   90 13-JAN-01 De Haan         De Haan; King
   90 17-JUN-03 King            De Haan; King
  100 16-AUG-02 Faviet          Faviet; Greenberg
  100 17-AUG-02 Greenberg       Faviet; Greenberg
  110 07-JUN-02 Gietz           Gietz; Higgins
  110 07-JUN-02 Higgins         Gietz; Higgins