In SQL, is there a way to split up a column in SQL that is stored as a list?

Question

I currently have a table that has a column stored as lists for whatever reason. It looks as follows:

Symbol      Exchange             Price
AAPL        [NYSE, CHX, PHLX]    22.02 
AAPL        [BSE, MS4X]          23.11

Is there a way to break up the table using SQL so it expands the columns to something like this:

Symbol      Exchange       Price
AAPL        NYSE           22.02 
AAPL        CHX            22.02
AAPL        PHLX           22.02
AAPL        BSE            23.11
AAPL        MS4X           23.11

Basically the goal is the break up the exchange column so that it has its own individual entries as lists.

Thanks

What is the data type of `exchange`? – Gordon Linoff Jul 22 '20 at 18:10 — Gordon Linoff, Jul 22 '20 at 18:10

score 1 · Accepted Answer · answered Jul 22 '20 at 18:11

1

Assuming it is an array, you can use unnest():

select t.symbol, u.exchange, t.price
from t cross join
     unnest(t.exchanges) u(exchange);

answered Jul 22 '20 at 18:11

Gordon Linoff

1,242,037
58
646
786

In SQL, is there a way to split up a column in SQL that is stored as a list?

1 Answers1