title: DB2 - LISTAGG() with DISTINCT clause - doesn't work?
body: <p>My query has a column with a small number of values in it, and I need to display them in a single field for each grouped result set - e.g. if I had an employee in 3 different departments, I'd want to see something like </p>
  
  <pre><code>EMPID          DEPTS                                   SOMENUMBER  SOMEOTHERNUMBER
  ------         ------                                  ----------- ---------------
  0001           ACCOUNTING, CUST SERVICE, CALL CENTER        100           200
  </code></pre>
  
  <p>The problem is when there are multiple duplicate departments for the employee. I see numerous questions on how to figure this out for Oracle and other DMBSs, but nothing specific to DB2. IBM's own documentation at <a href="https://www.ibm.com/support/knowledgecenter/SSFMBX/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0058709.html" rel="nofollow noreferrer">https://www.ibm.com/support/knowledgecenter/SSFMBX/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0058709.html</a> says:
  - "If DISTINCT is specified, duplicate string-expression values are eliminated.", and 
  - "If DISTINCT is specified for LISTAGG, the sort-key of the ORDER BY specification must match string-expression (SQLSTATE 42822). If string-expression is implicitly cast, the sort-key must explicitly include a corresponding matching cast specification.". </p>
  
  <p>As a simplistic example:</p>
  
  <pre><code>with mylist (field1) as 
      ( values 'A','A','B','C','D','A','C'
      )
      select listagg (distinct field1, ', ') 
          within group (order by field1) 
          from mylist;
  </code></pre>
  
  <p>The info on the IBM page suggests that this should return 'A, B, C, D' but instead I get 'A. A. A. B, C. C, D'. </p>
  
  <p>I should be able to get around this by having a subquery do some initial rolling up - e.g. </p>
  
  <pre><code>SELECT EMPLOYEE, LISTAGG(DEPARTMENT,', '),  
      SUM(SOMENUMBER) SOMENUMBER, SUM(SOMEOTHERNUMBER) SOMEOTHERNUMBER
  from (
      SELECT EMPLOYEE, DEPARTMENT, SUM(SOMENUMBER) SOMENUMBER, SUM(SOMEOTHERNUMBER) SOMEOTHERNUMBER
      FROM EMPLOYEES GROUP BY EMPLOYEE, DEPARTMENT)
  ) GROUP BY EMPLOYEE
  </code></pre>
  
  <p>and in fact I guess that's what I'll do, but the IBM documentation sure suggests the DISTINCT ought to do the trick. What am I missing?</p>