CODEWITHSUNDEEP
google-bigquery
dodonus
dodonus

Reputation: 11

Concatenate rows with same id in BigQuery from same table

I am running this query in google bigquery

SELECT rep.key.id as id, te.cmnt as comment
FROM `table` rep, UNNEST(tre) as te
WHERE u_id LIKE 'test'
ORDER BY cts DESC

and i am geting this

Id                    comment
---------------------------------
5165356444286976      HEloo
5165356444286976      TEST
5165356444286976

I want to merge all comments with same id, for example to look like this:

Id                    comment
---------------------------------
5165356444286976      HEloo, TEST

Upvotes: 0

Views: 1105

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59225

Use STRING_AGG():

WITH table AS (
  SELECT 'test' u_id, STRUCT(1 AS id) AS key, [STRUCT('hey' AS cmnt)] AS tre, 3 cts
  UNION ALL 
  SELECT 'test', STRUCT(1), [STRUCT('you')], 2
  UNION ALL 
  SELECT 'test', STRUCT(1), [STRUCT(CAST(null AS STRING))], 1
)


SELECT rep.key.id as id, STRING_AGG(te.cmnt, ', ' ORDER BY cts DESC) AS comments
FROM `table` rep, UNNEST(tre) as te
WHERE u_id LIKE 'test'
GROUP BY id

Upvotes: 1

Related Questions