3

Now I'm facing a problem that there are 5 tables in bigquery, image that table A have a record that in table B there are 5 records have some connections with it .Here is the example: Table A: record a Table B: record b,c,d,e,f

Now I'm using this sql:

select A.*,B.* from A join each B on A.xx = B.xx

and the query results like this:a,b;a,c;a,d;a,e;a,f;(show in 5 rows)

is there any idea to make the result like this:

a,b c d e f; (show in 1 row)

Hope your help! thanks!

Pentium10
  • 204,586
  • 122
  • 423
  • 502
xialin
  • 265
  • 1
  • 2
  • 11

2 Answers2

1

One way is to use GROUP_CONCAT

SELECT t1.c1,
       group_concat(t2.c2)
FROM
  (SELECT 'a' AS c1,
          1 AS k) t1
JOIN
  (SELECT *
   FROM
     (SELECT 'b' AS c2,
             1 AS k),
     (SELECT 'c' AS c2,
             1 AS k),
     (SELECT 'd' AS c2,
             1 AS k),
     (SELECT 'e' AS c2,
             1 AS k),
     (SELECT 'f' AS c2,
             1 AS k)) t2 ON t1.k=t2.k
GROUP BY t1.c1

this produces:

+-----+-------+-----------+---+
| Row | t1_c1 |    f0_    |   |
+-----+-------+-----------+---+
|   1 | a     | b,c,d,e,f |   |
+-----+-------+-----------+---+

the other is a using NEST (*disclaimer applies read further)

SELECT t1.c1,
       nest(t2.c2)
FROM
  (SELECT 'a' AS c1,
          1 AS k) t1
JOIN
  (SELECT *
   FROM
     (SELECT 'b' AS c2,
             1 AS k),
     (SELECT 'c' AS c2,
             1 AS k),
     (SELECT 'd' AS c2,
             1 AS k),
     (SELECT 'e' AS c2,
             1 AS k),
     (SELECT 'f' AS c2,
             1 AS k)) t2 ON t1.k=t2.k
GROUP BY t1.c1

But this must be written to a table as, BigQuery automatically flattens query results, so if you use the NEST function on the top level query, the results won't contain repeated fields. Use the NEST function when using a subselect that produces intermediate results for immediate use by the same query.

On the interface by default, BigQuery flattens all query results. To preserve nested and repeated results, select a destination table and enable Allow Large Results, then uncheck the Flatten results option.

*But there is a known bug with this feature when you are using to write to a destination table: Save a result set containing repeated field to a destination table - If you are not saving the result to a destination table it might be just good.

Community
  • 1
  • 1
Pentium10
  • 204,586
  • 122
  • 423
  • 502
1

To address limitation pointed in answer by Pentium10 - See workaround in answer for BigQuery creat repeated record field from query

It allows 

1. mimic NEST() for multiple fileds   
2. save result directly to table

thus addressing two current limitations 

a. NEST function accepts only one field  
b. NEST is not compatible with unFlatten Results Output
Community
  • 1
  • 1
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230