How to convert a column of array values

Question

How do I convert a column containing array values into separate columns:

Multiple rows, one column

{100,67,9}
{100,100}   
{100,100,100}
{100,9}

Multiple rows, multiple columns

100 67  9
100 100 
100 100 100
100 9

Gordon Linoff · Accepted Answer · 2019-12-11 10:56:32Z

2

A SQL query has a fixed set of columns. If you know the maximum number, then just extract the values:

select t.ar[1] as col1, t.ar[2] as col2, t.ar[3] as col3
from t;

If you don't know how many columns will be in the result set, then you would need to use dynamic SQL.

Here is a db<>fiddle.

answered Dec 11, 2019 at 10:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1