CODEWITHSUNDEEP
sqlgoogle-bigquery
KeylorNavas
KeylorNavas

Reputation: 151

Bigquery query turn table around

I have single table like that:

UserID    user_properties_key    user_properties_value(String) 
User1      email                     [email protected] 
           weight                      55

And I want to get result like that:

UserId           email           weight    
User1     [email protected]     55 
User2     [email protected]    155

My current query looks:

SELECT
  T1.UserId as UserId,
  T1.user_properties_value AS email,
  T2.user_properties_value AS weight,
FROM (FLATTEN([database20160814], user_properties_key )) AS T1
JOIN 
(FLATTEN([database20160814], user_properties_key )) AS T2
ON
  T1.userId = T2.userId
WHERE
  T1.user_properties_key="email"
  AND T2.user_properties_key="weight"
GROUP BY
  V0,
  V1,
  V2

If I'm trying to get more fields, query doesn't work or takes a long time

Upvotes: 2

Views: 98

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173003

Try below 

SELECT
  UserId,
  MAX(IF(user_properties.user_properties_key="email", user_properties.user_properties_value, NULL)) AS email,
  MAX(IF(user_properties.user_properties_key="weight", user_properties.user_properties_value, NULL)) AS weight
FROM [YourTable] 
GROUP BY UserId

OR 

SELECT
  UserId,
  MAX(IF(user_properties.user_properties_key="email", user_properties.user_properties_value, NULL)) WITHIN RECORD AS email,
  MAX(IF(user_properties.user_properties_key="weight", user_properties.user_properties_value, NULL)) WITHIN RECORD AS weight
FROM [YourTable]

It is not clear from your question, so I assumed your table is as below

enter image description here

See also Pivot Repeated fields in BigQuery

Upvotes: 1

Elliott Brossard
Elliott Brossard

Reputation: 33745

This is commonly known as a pivot table operation. A previous question and answer give an example: How to simulate a pivot table with BigQuery?

If you are using standard SQL, some of the functions and syntax in the example would need to be changed, but the principle is the same.

Upvotes: 0

Related Questions