如何獲取列 x GROUPed BY 列 y 的值列表？

我有以下表結構和數據（使用 Oracle DB 12c）：

CREATE TABLE authors (
   aid NUMBER(38) GENERATED ALWAYS AS IDENTITY CONSTRAINT authors_pk PRIMARY KEY NOT NULL,
   fname VARCHAR2(100 CHAR) NOT NULL,
   sname VARCHAR2(150 CHAR) NOT NULL,
   dob NUMBER(4),
   gender CHAR(1 CHAR)
);

CREATE UNIQUE INDEX authors_uind ON authors (fname, sname, dob, gender); --- Combination of columns must be unique

INSERT INTO authors(sname, fname, dob, gender) VALUES('Codd', 'Edgar F', 1923, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Date', 'Chris J', 1941, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Darwin', 'Hugh', 1943, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Lions', 'John', 1937, 'M');

CREATE TABLE publications (
   pid NUMBER(38) GENERATED ALWAYS AS IDENTITY CONSTRAINT publications_pk PRIMARY KEY NOT NULL,
   title VARCHAR2(150 CHAR) NOT NULL,
   written NUMBER(4)
);
CREATE INDEX publications_ind ON publications (title, written);

INSERT INTO publications(title, written) VALUES('A Relational Model of Data for Large Shared Data Banks', 1970);
INSERT INTO publications(title, written) VALUES('The Relational Model for Database Management', 1990);
INSERT INTO publications(title, written) VALUES('An Introduction to Database Systems', 2003);
INSERT INTO publications(title, written) VALUES('The Third Manifesto', 2000);
INSERT INTO publications(title, written) VALUES('Temporal Data and the Relational Model', 2002);
INSERT INTO publications(title, written) VALUES('Database in Depth: Relational Theory for Practitioners', 2005);
INSERT INTO publications(title, written) VALUES('Commentary on UNIX', 1976);

CREATE TABLE author_publications (
   aid NUMBER(38) REFERENCES authors (aid),
   pid NUMBER(38) REFERENCES publications (pid),
   CONSTRAINT author_publications_pk PRIMARY KEY (aid, pid)
);

INSERT INTO author_publications(aid, pid) VALUES(1, 1);
INSERT INTO author_publications(aid, pid) VALUES(1, 2);
INSERT INTO author_publications(aid, pid) VALUES(2, 3);
INSERT INTO author_publications(aid, pid) VALUES(2, 4);
INSERT INTO author_publications(aid, pid) VALUES(2, 5);
INSERT INTO author_publications(aid, pid) VALUES(2, 6);
INSERT INTO author_publications(aid, pid) VALUES(3, 4);
INSERT INTO author_publications(aid, pid) VALUES(3, 5);
INSERT INTO author_publications(aid, pid) VALUES(4, 7);

一位作者可能寫過多本書，而一本書可能由多名作者寫過。

查詢應該是什麼樣子，這樣我才能在每本書（所有書籍）中獲得一行，作者的姓名連接在一起。例如：

| Written | Title | PID | Authors |
|---------|-------|-----|---------|
| 1990 | THE THIRD MANIFESTO | 4 | DATE, DARWIN |
| 2002 | TEMPORAL DATA AND THE RELATIONAL MODEL | 8 | DARWIN, DATE |

*注 1：*第四列作者姓名的順序不重要，連續單個作者姓名後的逗號也不重要。

注 2：GROUP BY如果可以通過不同的方式獲得所需的結果，則不需要使用子句。

我的查詢如下：

--- Get a list of author names per title
SELECT p.written, upper(p.title), p.pid, concat(upper(a.sname), ', ') 
FROM publications p INNER JOIN author_publications apub on p.pid = apub.pid
INNER JOIN authors a on apub.aid = a.aid
GROUP BY p.pid
order by p.written, upper(p.title)
;

我收到的錯誤消息是“不是 GROUP BY 表達式”。（我不明白該語句的哪個特定部分失敗或為什麼失敗。）有問題的行是 SELECT 語句的開頭。

而不是CONCAT，使用LISTAGG：

select
 p.written, upper(p.title) as title, p.pid,
 upper(listagg(a.sname, ', ') within group (order by a.sname)) as authors
from publications p
join author_publications ap on p.pid = ap.pid
join authors a on ap.aid = a.aid
group by p.written, p.title, p.pid
order by p.written;

結果：

WRITTEN TITLE                                                         PID AUTHORS
------- ------------------------------------------------------------ ---- ---------------
  1970 A RELATIONAL MODEL OF DATA FOR LARGE SHARED DATA BANKS          1 CODD
  1976 COMMENTARY ON UNIX                                              7 LIONS
  1990 THE RELATIONAL MODEL FOR DATABASE MANAGEMENT                    2 CODD
  2000 THE THIRD MANIFESTO                                             4 DARWIN, DATE
  2002 TEMPORAL DATA AND THE RELATIONAL MODEL                          5 DARWIN, DATE
  2003 AN INTRODUCTION TO DATABASE SYSTEMS                             3 DATE
  2005 DATABASE IN DEPTH: RELATIONAL THEORY FOR PRACTITIONERS          6 DATE

引用自：https://dba.stackexchange.com/questions/212107