LISTAGG aggregate function

Examples for LISTAGG

• The following example produces an alphabetical list of comma-separated names, grouped by department, from the sample employee table.

  SELECT WORKDEPT,
   LISTAGG(LASTNAME, ', ') WITHIN GROUP(ORDER BY LASTNAME)
   AS EMPLOYEES
   FROM EMP
   GROUP BY WORKDEPT;

  The following result is returned.

  ---------+---------+---------+---------+---------+---------+---------+---------+---------+-------
  WORKDEPT  EMPLOYEES                                                             
  ---------+---------+---------+---------+---------+---------+---------+---------+---------+-------
  A00       HAAS, HEMMINGER, LUCCHESI, O'CONNELL, ORLANDO                         
  B01       THOMPSON                                                              
  C01       KWAN, NATZ, NICHOLLS, QUINTANA                                        
  D11       ADAMSON, BROWN, JOHN, JONES, LUTZ, PIANKA, SCOUTTEN, STERN, WALKER, YAMAMOTO, YOSHIMURA
  D21       JEFFERSON, JOHNSON, MARINO, MONTEVERDE, PEREZ, PULASKI, SMITH         
  E01       GEYER                                                                 
  E11       HENDERSON, PARKER, SCHNEIDER, SCHWARTZ, SETRIGHT, SMITH, SPRINGER     
  E21       ALONZO, GOUNOT, LEE, MEHTA, SPENSER, WONG                             
  

• FL 506 The following example shows how the result returned by the LISTAGG function can change if the sort-key expression references a column for which a column mask is defined. For this example, assume that an EMPLOYEE table is created with the following statement.

  CREATE TABLE EMPLOYEE ( STATE CHAR(2) NOT NULL,
                          SALARY INTEGER,             
                          BONUS  INTEGER,             
                          NAME  VARCHAR(20) NOT NULL); 
  

  Also assume that a column mask is defined on the SALARY column with the following CREATE MASK statement.

  CREATE MASK SALARY_MASK ON EMPLOYEE
      FOR COLUMN SALARY
          RETURN
              CASE
                  WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER, 'MANAGER') = 1) THEN
                      SALARY
                  ELSE  0
              END
      ENABLE;
  

  Also assume that the following query returns the following result when a manager issues it.

  SELECT STATE,                            
               CASE WHEN(SALARY >50000) THEN NAME
                        ELSE 'XXXX'                  
                    END AS NAME,                      
                SALARY                            
        FROM EMPLOYEE; 
  

  
         +---------------------------------------------------------+ 
         | STATE |     NAME    |         SALARY                    | 
         +---------------------------------------------------------+ 
         | CA    |     Jerry   |         80000                     | 
         | CA    |     Tom     |         70000                     | 
         | LU    |     XXXX    |         40000                     | 
         | LU    |     XXXX    |         10000                     | 
         | LU    |     XXXX    |         50000                     | 
         | MA    |     Sarah   |         90000                     | 
         + --------------------------------------------------------+ 
  

  Now consider the following LISTAGG statement.

  SELECT STATE,
             LISTAGG (NAME,',') WITHIN GROUP (ORDER BY SALARY ASC)
      FROM EMPLOYEE
      GROUP BY STATE;
  

  If a manager issues the example SELECT statement, it returns the following result. The column mask did not apply, so the names in each row are ordered based on the unmasked SALARY values.

  
      +----------------------------------------+ 
      | STATE |                                | 
      +----------------------------------------| 
      | CA    | Tom,Jerry                      | 
      | LU    | Ben,Linda,Mary                 | 
      | MA    | Sarah                          |
      +----------------------------------------+  
  

  However, if a non-manager employee issues the example SELECT statement, the column mask applies before the sort-key expression is evaluated. The result is that the masked SALARY value 0 is used for every employee when the ORDER BY is evaluated, so the names in each row are returned in a different order.

  
      +----------------------------------------+ 
      | STATE    |                             | 
      +----------------------------------------| 
      | CA       | Jerry,Tom                   | 
      | LU       | Linda,Ben,Mary              | 
      | MA       | Sarah                       | 
      +----------------------------------------+