将以逗号分隔的列数据拆分为其他列

问题描述

我在列中用逗号分隔数据:

I have comma separated data in a column:

Column
-------
a,b,c,d

我想将逗号分隔的数据分成多列以获得此输出:

I want to split the comma separated data into multiple columns to get this output:

Column1  Column2 Column3 Column4
-------  ------- ------- -------
a        b       c       d

如何实现?

推荐答案

如果CSV中的字段数恒定，则可以执行以下操作:

If the number of fields in the CSV is constant then you could do something like this:

select a[1], a[2], a[3], a[4]
from (
    select regexp_split_to_array('a,b,c,d', ',')
) as dt(a)

例如:

=> select a[1], a[2], a[3], a[4] from (select regexp_split_to_array('a,b,c,d', ',')) as dt(a);
 a | a | a | a
---+---+---+---
 a | b | c | d
(1 row)

如果CSV中的字段数不是恒定的，则可以通过以下方式获得最大字段数:

If the number of fields in the CSV is not constant then you could get the maximum number of fields with something like this:

select max(array_length(regexp_split_to_array(csv, ','), 1))
from your_table

，然后为您的查询构建适当的a[1], a[2], ..., a[M]列列表.因此，如果以上给出的最大值为6，则可以使用以下代码:

and then build the appropriate a[1], a[2], ..., a[M] column list for your query. So if the above gave you a max of 6, you'd use this:

select a[1], a[2], a[3], a[4], a[5], a[6]
from (
    select regexp_split_to_array(csv, ',')
    from your_table
) as dt(a)

如果需要，可以将这两个查询合并为一个函数.

You could combine those two queries into a function if you wanted.

例如，提供以下数据(在最后一行为NULL):

For example, give this data (that's a NULL in the last row):

=> select * from csvs;
     csv
-------------
 1,2,3
 1,2,3,4
 1,2,3,4,5,6

(4 rows)

=> select max(array_length(regexp_split_to_array(csv, ','), 1)) from csvs;
 max
-----
   6
(1 row)

=> select a[1], a[2], a[3], a[4], a[5], a[6] from (select regexp_split_to_array(csv, ',') from csvs) as dt(a);
 a | a | a | a | a | a
---+---+---+---+---+---
 1 | 2 | 3 |   |   |
 1 | 2 | 3 | 4 |   |
 1 | 2 | 3 | 4 | 5 | 6
   |   |   |   |   |
(4 rows)

由于分隔符是简单的固定字符串，因此您也可以使用 string_to_array 而不是regexp_split_to_array:

Since your delimiter is a simple fixed string, you could also use string_to_array instead of regexp_split_to_array:

select ...
from (
    select string_to_array(csv, ',')
    from csvs
) as dt(a);

感谢 Michael 关于此功能的提醒.

Thanks to Michael for the reminder about this function.

您真的应该重新设计数据库架构，以尽可能避免使用CSV列.您应该使用数组列或单独的表.

You really should redesign your database schema to avoid the CSV column if at all possible. You should be using an array column or a separate table instead.

这篇关于将以逗号分隔的列数据拆分为其他列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！