将逗号分隔的列数据拆分为其他列 [英] Split comma separated column data into additional columns

问题描述

我在一列中有逗号分隔的数据:

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.

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