CODEWITHSUNDEEP
google-bigquery
Brad Davis
Brad Davis

Reputation: 1170

Translate multiple fields per record into multiple records and a single field

I am trying to convert multiple fields per record (in this case three fields) into a single field over multiple records. For example, if the schema is

 userId, timestamp, field1, field2, field3

I'd like to convert that into

 userId, timestamp, field

e.g.

 abc123, 12:00:00, dog, dog, bird
 abc789, 12:00:01, wolf, sheep, horse

would become

 abc123, 12:00:00, dog
 abc123, 12:00:00, dog
 abc123, 12:00:00, bird
 abc789, 12:00:00, wolf
 abc789, 12:00:00, sheep
 abc789, 12:00:00, horse

The ordering doesn't matter

This is because the data for these fields was captured in parallel, but I want to be able to count the number of times particular values in those field show up regardless of whether they're in field1, field2, or field3. Specifically, I'd like to be able to count 2 dogs, 1, bird, 1, wolf, 1, sheep, and 1 horse.

Any suggestions?

Upvotes: 1

Views: 101

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33765

For completeness, you can achieve this using standard SQL (uncheck the "Use Legacy SQL" box under "Show Options") with an array literal; no need to concatenate and then split. For example,

SELECT
  userId,
  timestamp,
  [field1, field2, field3] AS field 
FROM YourTable;

Upvotes: 3

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

for BigQuery Legacy SQL: 

SELECT 
  userId, 
  timestamp, 
  SPLIT(CONCAT(field1, ',', field2, ',', field3)) AS field 
FROM YourTable

Upvotes: 2

Related Questions