如何实现一对多关系船数据作为昏迷分离，格式如下

Question

我有两个表，它是一对多关系(parent&chdild(。它是一个动态表，而不是固定数据。表中可以提供更多的数据。

user
Aid userName
1   author1
2   author2
3   author3
4   author4
etc
books
bid NAME Aid
1    x    1
2    y    1
3    z    2
4    z1   3

预期输出如下

'author1' AS author1,'x' AS x,'y' AS y,'author2' AS author2,'z' AS z,'author3' AS author3,'z1' AS z1

我已经尝试过使用父表，但如何获得上面格式中的所有行。

select listagg('''' || UserName || ''' as "' || UserName || '"', ',') within group (order by Aid)
from   (select distinct Aid,UserName  from user order by Aid);

Answer 1

首先，不要这样做。如果你想透视数据，可以在你用来访问数据库的第三方应用程序(Java、C#、PHP等(中进行，因为这将支持透视数据集，而不是试图强制它通过一些动态SQL并试图让查询做一些它不想做的事。

---

然而，如果你真的必须(不要(，那么：

SELECT LISTAGG('''' || name || ''' AS ' || name, ',')
WITHIN GROUP (ORDER BY name) AS names
FROM   (
SELECT username AS name
FROM   "USER"
UNION
SELECT name
FROM   books
)

对于样本数据：

CREATE TABLE "USER" (Aid, userName) AS
SELECT 1, 'author1' FROM DUAL UNION ALL
SELECT 2, 'author2' FROM DUAL UNION ALL
SELECT 3, 'author3' FROM DUAL UNION ALL
SELECT 4, 'author4' FROM DUAL;
CREATE TABLE books (bid, NAME, Aid) AS
SELECT 1, 'x',  1 FROM DUAL UNION ALL
SELECT 2, 'y',  1 FROM DUAL UNION ALL
SELECT 3, 'z',  2 FROM DUAL UNION ALL
SELECT 4, 'z1', 3 FROM DUAL;

输出：

NAMES
'author1'AS author1，'author2'AS author2，'author3'AS author3，'author 4'AS author4，'x'AS x，'y'AS y，'z'AS z，'z1'AS z1