In python, we can convert a,b,c,d to 4 rows easily. But using SQL to do this is not very common. Here is the solution which I found at work.
I finally found a solution to resolve the ‘,’ issue below referring to https://www.holistics.io/blog/splitting-array-string-into-rows-in-amazon-redshift-or-mysql/
with test as
(
select matching_rs, count(distinct asin) as counted_asin
from table_XX
where matching_rs like ‘%,%’
group by 1
)
, NS AS (
select 1 as n union all
select 2 union all
select 3
)
select
TRIM(SPLIT_PART(test.matching_rs, ‘,’, NS.n))
from NS
inner join test
ON NS.n <= REGEXP_COUNT(test.matching_rs, ‘,’) + 1
I used 3 in NS because the following query gave us 3.
select max(REGEXP_COUNT(matching_rs, ‘,’) + 1 ) as counted_names
from table_XX
Also, in the opposite way, if you want to convert 4 rows a b c d to 1 cell with a,b,c,d, just use listagg function in SQL.
Leave a Reply