SPLIT in BigQuery - Stack Overflow

admin管理员组

文章数量:1405319

The table structure is as below

TABLE A

ID	STATUS	STATUS CODE
10	ABS	1,2
11	ABS	3,4

The table structure is as below

TABLE A

ID	STATUS	STATUS CODE
10	ABS	1,2
11	ABS	3,4

How to split as

ID	STATUS Code
10	1
10	2
11	3
11	4

This is what I tried but the ID is not repeating

WITH T_LIST AS (
select id, status, split(status_code, ',') as code_array
from TABLE A 
where status is not null
and status = 'ABS'
)
SELECT TL.id, TL.code_array
FROM T_LIST TL 
CROSS JOIN UNNEST(TL.code_array) as INFO

ID	STATUS Code
10	1
	2
11	3
	4

• google-bigquery

Share Improve this question Follow edited Mar 24 at 16:46 DarkBee 15.5k88 gold badges7272 silver badges117117 bronze badges asked Mar 24 at 16:25 AndyKashAndyKash 5322 silver badges99 bronze badges 0

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I replicate your query with the use of SPLIT and CROSS JOIN UNNEST and it gives me your expected result. Split in status code to have your values in array and have CROSS JOIN UNNEST properly to have them group together.

Table:

ID	Status	Code
10	ABS	1,2
11	ABS	3,4

Here is the code I use:

WITH T\_LIST AS (

SELECT id, status, SPLIT(CODE, ',') AS code\_array

FROM data-sandbox-\*\*\*\*\*\*.SIB3.SIB4

WHERE status IS NOT NULL

AND status = 'ABS'

)

SELECT TL.id, INFO AS CODE

FROM T\_LIST TL

CROSS JOIN UNNEST(TL.code\_array) AS INFO

ORDER BY TL.id, INFO;

Here is the screenshot of my replication:

本文标签： SPLIT in BigQueryStack Overflow