CODEWITHSUNDEEP
sqlgoogle-bigquery
rinrin0000
rinrin0000

Reputation: 83

I want to aggregate data each same date

I want to aggregate data each same date. I wrote SQL in BigQuery. Now there is a table like 

id | date                  | country | code
1    2015-10-16T12:00:30     Japan     A
2    2015-10-16T12:00:30     America   C
3    2017-08-20T15:50:44     America   B
4    2016-22-56T11:32:01     China     E
5    2017-08-20T15:50:44     EU        D

I want to change upper tabble into this


id    | date                  | country       | code
1,2    2015-10-16T12:00:30     Japan,America    A,C
3,5    2017-08-20T15:50:44     America,EU       B,D
4      2016-22-56T11:32:01     China            E

It is like put together id・country・code in each date.

How should I write query to do this?

Upvotes: 0

Views: 118

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

You can use bigquery string_agg function in standard sql.

select string_agg(id, ',') as id
    , string_agg(country, ',') as country
    , string_agg(code, ',') as code
from tableA
group by date

Note: You need to enable first standard sql support. Using this link

Legacy SQL, you can use group_concat() function. See this link.

 select group_concat(id, ',') as id
        , group_concat(country, ',') as country
        , group_concat(code, ',') as code
 from tableA
 group by date

Upvotes: 1

Related Questions