I have a BigQuery table which has data as shown below image

I wish to create a table out of this data which is as shown below image

So here I wish to

1. remove the email column data
2. combine the emp_type column values as comma separated value
3. have just 1 row per id

I tried using STRING_AGG function of BigQuery but was unable to achieve what I specified above.

The table actually has more than 30 columns but for the sake of explaining the issue i reduced it to 7 columns.

How do I combine multiple rows as one in a query?

CodePudding user response：

I see two possible options, if you want to have uniqe row per combination of all parameters except email and emp_type:

SELECT id, name, status, `count`, is_hybrid, STRING_AGG(emp_type, ', ')
FROM data
GROUP BY id, name, status, `count`, is_hybrid

If you want to have just one row per id, you can group by id and select arbitrary value(from rows with this id) for other columns:

SELECT id, ANY_VALUE(name), ANY_VALUE(status), ANY_VALUE(`count`), ANY_VALUE(is_hybrid), STRING_AGG(emp_type, ', ')
FROM data
GROUP BY id