如何取消嵌套/分解/展平 Amazon Redshift 列中的逗号分隔值? [英] How to unnest/explode/flatten the comma separated value in a column in Amazon Redshift?

问题描述

我正在尝试为 col2 中的每个值生成一个新行.由于该值是字符串格式，因此在使用任何 Redshift json 函数之前，我需要将其用双引号括起来.

I am trying to generate a new row for each value in col2. As the value is in string format, I need to wrap it in double quotes before using any Redshift json function on it.

输入:

col1(int)       col2(varchar)
1               ab,cd,ef
2               gh
3               jk,lm,kn,ut,zx

输出:

col1(int)       col2(varchar)
1               ab
1               cd
1               ef
2               gh
3               jk
3               lm
3               kn
3               ut
3               zx

推荐答案

    with NS AS (
      select 1 as n union all
      select 2 union all
      select 3 union all
      select 4 union all
      select 5 union all
      select 6 union all
      select 7 union all
      select 8 union all
      select 9 union all
      select 10
    )
    select
      TRIM(SPLIT_PART(B.col2, ',', NS.n)) AS col2
    from NS
    inner join table B ON NS.n <= REGEXP_COUNT(B.col2, ',') + 1

这里，NS(数字序列)是一个 CTE，它返回一个从 1 到 N 的数字列表，这里我们必须确保我们的最大数量大于我们最大标签的大小，因此您可以尝试添加列表中的更多数字取决于您的上下文.

Here, the NS (number sequence) is a CTE that returns a list of number from 1 to N, here we have to make sure that our max number is greater than the size of our maximum tags, so you can try adding more numbers to the list depending on your context.

这篇关于如何取消嵌套/分解/展平 Amazon Redshift 列中的逗号分隔值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！