将逗号分隔值拆分到具有固定列数的目标表中

我在 Postgres 13.1 数据库中有一个包含单列的表。它由许多带有逗号分隔值的行组成 - 最多大约 20 个元素。

我想将数据分成多列。但我的列数有限，例如单行中有 5 个和 5 个以上的 CSV 值，因此多余的值必须转移到新的/下一行）。这个怎么做？

Example:

a1, b1, c1
a2, b2, c2, d2, e2, f2
a3, b3, c3, d3, e3, f3, g3, h3, i3, j3
a4
a5, b5, c5
'
'
'

列只有 5，因此输出如下：

c1 c2 c3 c4 c5
---------------
a1 b1 c1
a2 b2 c2 d2 e2 
f2
a3 b3 c3 d3 e3
f3 g3 h3 i3 j3
a4
a5 b5 c5
'
'
'

将 CSV 值存储在单列中通常是糟糕的设计。如果可能的话，请使用数组或适当标准化的设计。

虽然坚持你目前的情况......

对于已知的较小最大元素数

一个没有欺骗或递归的简单解决方案就可以了：

SELECT id, 1 AS rnk
     , split_part(csv, ', ', 1) AS c1
     , split_part(csv, ', ', 2) AS c2
     , split_part(csv, ', ', 3) AS c3
     , split_part(csv, ', ', 4) AS c4
     , split_part(csv, ', ', 5) AS c5
FROM   tbl
WHERE  split_part(csv, ', ', 1) <> '' -- skip empty rows

UNION ALL
SELECT id, 2
     , split_part(csv, ', ', 6)
     , split_part(csv, ', ', 7)
     , split_part(csv, ', ', 8)
     , split_part(csv, ', ', 9)
     , split_part(csv, ', ', 10)
FROM   tbl
WHERE  split_part(csv, ', ', 6) <> '' -- skip empty rows

-- three more blocks to cover a maximum "around 20"

ORDER  BY id, rnk;

数据库小提琴here

id是原表的PK。
显然，这假定“,”作为分隔符。
您可以轻松适应。

Related:

• 将逗号分隔的列数据拆分为附加列

对于未知数量的元素

各种方式。一种方式使用regexp_replace()在取消嵌套之前替换每五个分隔符...

-- for any number of elements
SELECT t.id, c.rnk
     , split_part(c.csv5, ', ', 1) AS c1
     , split_part(c.csv5, ', ', 2) AS c2
     , split_part(c.csv5, ', ', 3) AS c3
     , split_part(c.csv5, ', ', 4) AS c4
     , split_part(c.csv5, ', ', 5) AS c5
FROM   tbl t
     , unnest(string_to_array(regexp_replace(csv, '((?:.*?,){4}.*?),', '\1;', 'g'), '; ')) WITH ORDINALITY c(csv5, rnk)
ORDER  BY t.id, c.rnk;

数据库小提琴here

这假设所选的分隔符; never出现在你的字符串中。 （就像,永远不可能出现。）

正则表达式模式是关键：'((?:.*?,){4}.*?),'

(?:) ... “非捕获”括号组
() ... “捕获”一组括号
*? ... 非贪婪量词
{4}?...正好 4 个匹配的序列

替代品'\1;'包含反向引用 \1.

'g'由于第四个函数参数需要重复替换。

进一步阅读：

• 在文本数组上应用 `trim()` 和 `regexp_replace()`
• PostgreSQL unnest() 与元素编号

解决此问题的其他方法包括递归 CTE 或集合返回函数......

从右向左填充

（就像你添加的如何将从右侧开始的值放入列中？)
只需倒数数字，例如：

SELECT t.id, c.rnk
     , split_part(c.csv5, ', ', 5) AS c1
     , split_part(c.csv5, ', ', 4) AS c2
     , split_part(c.csv5, ', ', 3) AS c3
     , split_part(c.csv5, ', ', 2) AS c4
     , split_part(c.csv5, ', ', 1) AS c5
FROM ...

数据库小提琴here