How to convert an array to multiple columns in big query

Question

I am trying to sort out how to change this array so that it shows the returned values across multiple columns instead of together.

SELECT
handle,
fdc.identifiers
FROM table.fdc

Code returns the following

Handle	Identifier
1234	3123
	1233
	1232
3456	1231
	3411
2321
1235

The code returns all of the identifiers in one column. Can they be split out to multiple columns?

I would like the table to look like this below:

Handle	Identifier
1234	3123	1233	1232
3456	1231	3411
2321
1235

The code returns all of the identifiers in one column. Can they be split out to multiple columns?

Can you post an example of what the output table should look like? — randomdatascientist
– randomdatascientist, Commented Dec 3, 2021 at 3:15
Edited the post with a new table to demonstrate what I need. Each Handle can have a unique amount of identifiers or be null. The extra columns can be labeled if needed. — Nova700
– Nova700, Commented Dec 3, 2021 at 3:33
If the number of identifiers is indeterminate it might make more sense to return them in an ARRAY column. See this answer by Mikhail Berlyant: stackoverflow.com/a/41110942/15921941 — randomdatascientist
– randomdatascientist, Commented Dec 3, 2021 at 3:40
The results are already in an array, so any type of array_agg function doesn't work. Any other ideas? — Nova700
– Nova700, Commented Dec 3, 2021 at 4:35

Mikhail Berlyant · Accepted Answer · 2021-12-03 04:21:49Z

1

Consider below approach

select * from (
  select handle, val, offset
  from your_table t
  left join t.identifiers val with offset
)
pivot (min(val) as identifier for offset in (0, 1, 2))

if to apply to dummy data as in your question

with your_table as (
  select 1234 handle, [3123, 1233, 1232] identifiers union all
  select 3456, [1231, 3411] union all
  select 2321, [] union all 
  select 1235, []
)

output is

answered Dec 3, 2021 at 4:21

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

1 Comment

Nova700 Over a year ago

thanks for the help! I literally have 0 understanding of what's going on in your code. any chance you can explain it? specifically with offset and pivot, never used them before. I also need to have a where clause that specifies a date range. I tried to add that to the code and it couldn't recognize any date range no matter how I labeled anything.

Collectives™ on Stack Overflow

How to convert an array to multiple columns in big query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related