Creating new columns based on existing column storing a string

Question

Given a table with users' data and their purchase history, stored as a string - I'd like to create using SQL new columns (column per product) that we'll indicate whether the customer purchased this product. Current table:

user_number	products
12	(4,2,8,10,20)
13	(9,3,8,15)
52	(8,14)
4	(10)

Desired table:

user_number	2	3	4	8	9	10	14	15
12	1	0	1	1	0	1	0	0
13	0	1	0	1	1	0	0	1
52	0	0	0	1	0	0	1	0
4	0	0	0	0	0	1	0	0

Answer 1

Consider below approach

execute immediate (
select ''' select * from (
  select user_number, product
  from your_table, unnest(split(trim(products, '()'))) product
) pivot (count(*) for '_' || product in (''' || string_agg('"' || col || '"', ',' order by cast(product as int64)) || '''))
''' from (
  select distinct '_' || product as col, product, 
  from your_table, unnest(split(trim(products, '()'))) product  
))           

if applied to sample data in your question - output is