Bigquery query turn table around

Question

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

score 1 · Accepted Answer · edited May 23 '17 at 12:34

1

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

See also Pivot Repeated fields in BigQuery

edited May 23 '17 at 12:34

Community

1
1

answered Aug 19 '16 at 13:38

Mikhail Berlyant

165,386
8
154
230

Thanks. It works! =) But i have one problem, one user can have many values(how result i have resul in field 'weight' Optional(32) or similar,and how i can get last value for this issue. Thanks. – KeylorNavas Aug 19 '16 at 14:19
Add specific example to you question otherwise it is not clear enough – Mikhail Berlyant Aug 19 '16 at 14:22
I constantly update the data. How can i get last recorded value? – KeylorNavas Aug 19 '16 at 14:43
it is still not clear to me what exactly your `new question` is! i recommend you to ask new question and provide all relevant details there. i will be more than happy to try to answer new one – Mikhail Berlyant Aug 19 '16 at 15:42

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

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.

edited May 23 '17 at 10:33

Community

1
1

answered Aug 19 '16 at 13:31

Elliott Brossard

32,095
2
67
99

Bigquery query turn table around

2 Answers2