在 Postgres 把同一欄的資料，併成同一列的逗號分隔值呈現

雖然不清楚這種功能要作什麼，還是紀錄一下～

在 Oracle 資料庫裡面，要把同一欄位的不同筆資料，用逗號分隔的方式輸出，有以下兩種方法
(一) 用 XML 處理，比較像繞遠路

SQL> select DEPARTMENT_ID, 
   rtrim (xmlagg (xmlelement (e, FIRST_NAME || ',')).extract ('//text()'), ',') enames 
 from EMPLOYEES 
 group by DEPARTMENT_ID;

DEPARTMENT_ID
-------------
ENAMES
--------------------------------------------------------------------------------
           10
Jennifer

           20
Michael,Pat

           30
Den,Karen,Guy,Sigal,Shelli,Alexander


DEPARTMENT_ID
-------------
ENAMES
--------------------------------------------------------------------------------
           40
Susan

           50
Matthew,Douglas,Donald,Kevin,Alana,Vance,Samuel,Britney,Sarah,Randall,Timothy,Jennifer,Kelly,Anthony,Julia,Alexis,Nandita,Girard,Martha,Jean,Winston,Peter,Randall,Curtis,Trenna,Joshua,John,Stephen,Renske,Hazel,Ki,Michael,Jason,TJ,James,Mozhe,Laura,Steven,James,Irene,Julia,Kevin,Shanta,Payam,Adam


DEPARTMENT_ID
-------------
ENAMES
--------------------------------------------------------------------------------
           60
Alexander,Diana,Valli,David,Bruce

           70
Hermann

           80
John,Charles,Jack,Jonathon,Alyssa,Ellen,Sundita,Elizabeth,William,Tayler,Harrison,Lisa,Amit,Sundar,David,Mattea,Danielle,Clara,Sarath,Louise,Lindsey,Allan,Patri

DEPARTMENT_ID
-------------
ENAMES
--------------------------------------------------------------------------------
ck,Janette,Oliver,Nanette,Christopher,Peter,David,Peter,Eleni,Gerald,Alberto,Karen
           90
Steven,Lex,Neena
          100
Nancy,Luis,Jose Manuel,Ismael,John,Daniel

DEPARTMENT_ID
-------------
ENAMES
--------------------------------------------------------------------------------
          110
Shelley,William

Kimberely

12 rows selected.

或是
(二) 用 listagg() 函數處理

SQL> SELECT
   DEPARTMENT_ID,
   listagg (FIRST_NAME, ',') 
WITHIN GROUP 
(ORDER BY FIRST_NAME) FIRST_NAME 
FROM EMPLOYEES 
GROUP BY DEPARTMENT_ID;


DEPARTMENT_ID
-------------
FIRST_NAME
--------------------------------------------------------------------------------
           10
Jennifer

           20
Michael,Pat

           30
Alexander,Den,Guy,Karen,Shelli,Sigal


DEPARTMENT_ID
-------------
FIRST_NAME
--------------------------------------------------------------------------------
           40
Susan

           50
Adam,Alana,Alexis,Anthony,Britney,Curtis,Donald,Douglas,Girard,Hazel,Irene,James,James,Jason,Jean,Jennifer,John,Joshua,Julia,Julia,Kelly,Kevin,Kevin,Ki,Laura,Martha,Matthew,Michael,Mozhe,Nandita,Payam,Peter,Randall,Randall,Renske,Samuel,Sarah,Shanta,Stephen,Steven,TJ,Timothy,Trenna,Vance,Winston


DEPARTMENT_ID
-------------
FIRST_NAME
--------------------------------------------------------------------------------
           60
Alexander,Bruce,David,Diana,Valli

           70
Hermann
           80
Alberto,Allan,Alyssa,Amit,Charles,Christopher,Clara,Danielle,David,David,Eleni,Elizabeth,Ellen,Gerald,Harrison,Jack,Janette,John,Jonathon,Karen,Lindsey,Lisa,Lou

DEPARTMENT_ID
-------------
FIRST_NAME
--------------------------------------------------------------------------------
ise,Mattea,Nanette,Oliver,Patrick,Peter,Peter,Sarath,Sundar,Sundita,Tayler,William
           90
Lex,Neena,Steven
          100
Daniel,Ismael,John,Jose Manuel,Luis,Nancy

DEPARTMENT_ID
-------------
FIRST_NAME
--------------------------------------------------------------------------------
          110
Shelley,William

Kimberely

12 rows selected.

上面這兩方式在 EDB 都還沒支援（截至 9.6），那應該如何處理？




在 Postgres 底下可以使用 array_agg() 處理，便能把不同欄位資料塞在一起，放到 array 裡面。如果不要 array 的大括號，只要再多套用  array_to_string() 就行了。

edb=# -- 先列出來
edb=# select deptno, ename
from emp
group by ROLLUP(deptno, ename)
order by deptno;
 deptno | ename  
--------+--------
     10 | CLARK
     10 | KING
     10 | MILLER
     10 | 
     20 | ADAMS
     20 | FORD
     20 | JONES
     20 | SCOTT
     20 | SMITH
     20 | 
     30 | ALLEN
     30 | BLAKE
     30 | JAMES
     30 | MARTIN
     30 | TURNER
     30 | WARD
     30 | 
        | 
(18 rows)

edb=# select deptno, array_agg(ename) AS ename
from emp      
group by deptno
order by deptno;
 deptno |                 ename                  
--------+----------------------------------------
     10 | {CLARK,KING,MILLER}
     20 | {SMITH,JONES,SCOTT,ADAMS,FORD}
     30 | {ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES}
(3 rows)

edb=# select deptno,
   array_to_string( array_agg(ename), ',') AS ename
from emp
group by deptno
order by deptno;
 deptno |                ename                 
--------+--------------------------------------
     10 | CLARK,KING,MILLER
     20 | SMITH,JONES,SCOTT,ADAMS,FORD
     30 | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
(3 rows)

或是！直接用 string_agg() 函數，更簡單...

edb=# select deptno, string_agg(ename, ',') AS ename 
from emp  
group by deptno 
order by deptno;
 deptno |                ename                 
--------+--------------------------------------
     10 | CLARK,KING,MILLER
     20 | SMITH,JONES,SCOTT,ADAMS,FORD
     30 | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
(3 rows)

看起來在 Postgres 比較簡單處理～

---

[補充]
若是有排序的需要，在 Oracle 裡面透過 WITHIN GROUP 作 ORDER BY 處理

SQL> conn hr/hr
Connected.
SQL> SELECT LISTAGG(last_name, ';') 
WITHIN GROUP (ORDER BY hire_date) "Emp_list", 
MIN(hire_date) "Earliest" 
FROM employees 
WHERE department_id = 20;
  2    3    4    5  
Emp_list
--------------------------------------------------------------------------------
Earliest
------------------
Hartstein;Fay
17-FEB-04

但是在 PGSQL 中，string_agg() 卻不接受 WITHIN GROUP (ORDER BY ...) 的修飾

edb=# select string_agg(ename, ',') within group (ORDER BY hiredate) as name_list, 
MIN(hiredate) earliest 
from emp where deptno = 20;
ERROR:  function string_agg(character varying, unknown, timestamp without time zone) does not exist
LINE 1: select string_agg(ename, ',') within group (ORDER BY hiredat...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

這是因為 string_agg() 不是 Ordered-set aggregate 的緣故，無法接受 WITHIN GROUP() 語法。不過 PGSQL 的 Aggregation Function 可以允許把 ORDER BY 放在函數的參數之內；只要我們把  ORDER BY 只能放在 Aggregation Function 的所有參數最後就好

edb=# select string_agg(ename, ',' ORDER BY hiredate) as name_list, 
MIN(hiredate) earliest 
from emp where deptno = 20;
          name_list           |      earliest      
------------------------------+--------------------
 SMITH,JONES,FORD,SCOTT,ADAMS | 17-DEC-80 00:00:00
(1 row)

edb=# -- ORDER BY 放在錯誤位置會出錯
edb=# select string_agg(ename ORDER BY hiredate, ',') as 
name_list, 
MIN(hiredate) earliest 
from emp where deptno = 20;
ERROR:  function string_agg(character varying) does not exist
LINE 1: select string_agg(ename ORDER BY hiredate, ',') as name_list...
               ^
HINT:  No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate.

參考：
Oracle SQL: displaying multiple columns per row
sql - Combining multiple rows in postgreSQL into one row? - Stack Overflow
Re: Multiple rows into one row
sql - Concatenate multiple result rows of one column into one, group by another column - Stack Overflow

Using array_agg in Postgres – powerful and flexible - Craig Kerstiens
The many faces of DISTINCT in PostgreSQL – Statuscode – Medium
sql - Concatenate multiple result rows of one column into one, group by another column - Stack Overflow
EnterpriseDB 的客服服務（Customer Portal）