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?

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-07 10:56:25Z

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 7, 2019 at 10:56

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fahmi · Accepted Answer · 2019-03-07 10:55:01Z

1

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

answered Mar 7, 2019 at 10:55

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Collectives™ on Stack Overflow

Standard SQL in BigQuery - Get concatenate text from multiple rows

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related