CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
maxisme
maxisme

Reputation: 4245

Iterating comma seperated values in SQL

I am looking to convert the table:

+-----+-------+-----+
| Foo |  Bar  | Baz |
+-----+-------+-----+
| a   | 1,2,3 |   A |
| a   |   1,2 |   B |
| b   | 1,5,6 |   A |
+-----+-------+-----+

to:

+--------+-----+
| BarBaz | Foo |
+--------+-----+
| 1A     | a,b |
| 2A     | a   |
| 3A     | a   |
| 1B     | a   |
| 2B     | a   |
| 5A     | b   |
| 6A     | b   |
+--------+-----+

Upvotes: 1

Views: 60

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

so you can use the old style join , with split_to_table like so:

with data(foo, bar, baz) as (
    select * from values
      ('a', '1,2,3', 'A'),
      ('a', '1,2', 'B'),
      ('b', '1,5,6', 'A')
)
select concat(s.value,baz) AS BarBaz
      , listagg(foo, ',') AS Foo
from data t, 
    lateral split_to_table(t.bar , ',') s
group by BarBaz
order by BarBaz;

gives you:

BARBAZ  FOO
1A      a,b
1B      a
2A      a
2B      a
3A      a
5A      b
6A      b

or change to the cross join form by adding a table() wrapper with the join section being like:

from data t
cross join table(split_to_table(t.bar , ',')) s

or use the FLATTEN and SPLIT form like:

from data t
cross join table(flatten(split(t.bar , ','))) s

or the old join , and a LATERAL FLATTEN like:

from data t,
LATERAL flatten(split(t.bar , ',')) s

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

here is how you can do it:

select concat(barn,baz) BarBaz
      , listagg(foo, ',') Foo
from table t
cross join split(t.Bar , ',') splittedbars
group by concat(splittedbars,baz)

or

select concat(b.value::string,baz) BarBaz
      , listagg(foo, ',') Foo
from table t, lateral flatten(input=> split(t.Bar , ',')) b
group by concat(b.value::string,baz)

Upvotes: 1

Related Questions