在多行中的逗号之间分配表达式

Question

我在Postgres服务器中有一个SQL表，每个id有几个名称，我需要按行分布它们。文件是这样的：

| Id       | Names                       |
| AA00001X | "Agent_1, Agent_2, Agent_3" |
| BA00002X | "Agent_1, Agent_6"          |
| CA00002X | "Agent_4, Agent_2"          |
| DA00001Y | "Agent_2"                   |

我需要的是这样的表格：

| Id        | Name      |
| AA00001X  | "Agent_1" |
| AA00001X  | "Agent_2" |
| AA00001X  | "Agent_3" |
| BA00002X  | "Agent_1" |
| BA00002X  | "Agent_6" |
| CA00002X  | "Agent_4" |
| CA00002X  | "Agent_2" |
| DA00001Y  | "Agent_2" |

我尝试使用 R 接口并构建一个能够做到这一点的代码。尽管如此，初始文件很大(3.1 Go(，由于我的配置，R 无法处理它。我认为直接在SQL上执行此操作会更容易，但我不是SQL代码专家。

Answer 1

您可以将列表转换为数组，然后取消嵌套数组：

select t.id, trim(x.name) as name
from the_table t
cross join lateral unnest(string_to_array(trim(both '"' from t.names), ',')) as x(name)
order by t.id;

trim(both '"' from t.names)在将逗号分隔的字符串转换为数组之前，从逗号分隔的字符串中删除双引号。

在线示例：https://rextester.com/KGD2704

Answer 2

试试这个：

SELECT DISTINCT T2.id, 
SUBSTRING(
(
SELECT ','+T1.Name  AS [text()]
FROM dbo.mytable T1
WHERE T1.id = T2.id
ORDER BY T1.id
FOR XML PATH ('')
), 2, 1000) [Names]
FROM dbo.mytable T2

您必须使用您的表名而不是"mytable">

你也可以使用这篇文章中的答案之一：如何在PostgreSQL"group by"查询中连接字符串字段的字符串？