CODEWITHSUNDEEP
google-bigquery
Maykid
Maykid

Reputation: 517

How do I concatenate a column by specific ID of another column?

The current problem that I'm having currently is, I have two specific columns within my BigQuery instance both have ID's but one column is more static then the other. The data set looks something like this:

     ID A          |           ID B
       1                       123
       1                       456
       1                       789
       2                       333
       2                       898
       2                       999
       3                       111
       3                       222
       3                       444

I'm not sure where to actually begin with this problem as I believe it could be possible but might not be.

What I'm trying to get accomplished is to have the data come back as:

     ID A       |       Result
      1             123; 456; 789;
      2             333; 898; 999;
      3             111; 222; 444;

This is the result that I am looking for where everything is rolled up by ID A, and the ID B is rolled up but separated by ';'. How would I be able to do this?

If further clarification is needed please let me know and I'll do my best to explain further.

-Maykid

Upvotes: 1

Views: 61

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59225

Use STRING_AGG(DISTINCT ORDER BY):

SELECT id_a, STRING_AGG(DISTINCT id_b, '; ' ORDER BY id_b) result
FROM (
     SELECT 'a' id_a, 'b' id_b UNION ALL SELECT 'a', 'c'
)
GROUP BY id_a

enter image description here

Upvotes: 2

Related Questions