KeylorNavas KeylorNavas - 3 months ago 9
SQL Question

Bigquery query turn table around

I have single table like that:

UserID user_properties_key user_properties_value(String)
User1 email user_email@gmail.com
weight 55


And I want to get result like that:

UserId email weight
User1 user_email@gmail.com 55
User2 user2_email@email.com 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

Answer

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