Standard SQL in BigQuery - Get concatenate text from multiple rows

Question

I have tables like:

document:

+-----+---------+
| dId | score   |
+-----+---------+
| A   | 100     |
| B   | 80      |
| C   | 70      |
+-----+---------+

entity:

+------+------------+-----+
| name | details    | dId |
+------+------------+-----+
| e1   | a          |   A |
| e2   | a          |   A |
| e3   | b          |   B |
| e4   | c          |   B |
| e5   | d          |   C |
+------+------------+-----+

Expected Output:

+------+--------+----------+
| dId  | score  | entities |
+------+--------+----------+
| A    | 100    |   e1, e2 |
| B    | 80     |   e3, e4 |
| C    | 70     |   e5     |
+------+--------+----------+

Current Query:

SELECT
  docT.dId,
  docT.score,
  entityT.name AS entities
FROM
  document docT,
  entity entityT
LEFT JOIN
  document_sentiment docT1
ON
  docT1.dId = entityT.dId

Now, I've gone through 1 and 2 which are for SQL-Server.

But I'm looking for Standard SQL Format used by BigQuery.

How can I get the expected output with Standard SQL format?

score 2 · Accepted Answer · answered Mar 07 '19 at 10:56

2

try like below

  select d.dId,score,STRING_AGG(e.name) 
 document d join entity e on d.did=e.did
  group by d.dId,score

answered Mar 07 '19 at 10:56

Zaynul Abadin Tuhin

30,345
5
25
56

score 1 · Answer 2 · answered Mar 07 '19 at 10:55

use array_agg() - reference

SELECT
  docT.dId,
  docT.score,
  array_agg(entityT.name) AS entities
FROM
document docT join entity entityT on docT.dId=entityT.dId
LEFT JOIN document_sentiment docT1 ON docT1.dId = entityT.dId
group by docT.dId,docT.score

Standard SQL in BigQuery - Get concatenate text from multiple rows

2 Answers2