Struggling to Group and Contact 2 columns with SQL-BigQuery

Question

Im strugging with something very basic :/

Given the following table:

| Id   | Website  |       email       | companyid_1 | companyid_2 |
| SL2  | test.co  |  [email protected]   |   2051      |    2051     |
| SL2  | test.co  |  [email protected]   |   4459      |    2051     |

I would like this output:

| Id   | Website  |       email       |    New Field  |
| SL2  | test.co  |  [email protected]   |   2051, 4459  |

Thanks for your help.

I used the concat formula but in some cases does not work. I think its maybe beucase the format of these values are different (string vs not string)

Im ussing the contact query but the output I get is not the expected one

Output
|    New Field  |
|   2051, 4459, 2051  |

Answer 1

You can consider below

WITH sample_table AS (
  SELECT 'SL2' Id, 'test.co' Website, '[email protected]' email, '2051' companyid_1, '2051' company_id2 UNION ALL
  SELECT 'SL2' Id, 'test.co' Website, '[email protected]' email, '4459' companyid_1, '2051' company_id2
)
SELECT Id, Website, email, STRING_AGG(DISTINCT company_id) New_Field
  FROM sample_table, UNNEST([companyid_1, company_id2]) company_id
 GROUP BY 1, 2, 3;

Query results