Big Query or SQL reshape data

Question

im use bigquery for storage data For example im have table

userId|event  |count
------------- |
1     |event1 |1
1     |event2 |2
2     |event1 |2
2     |event2 |1 
2     |event3 |4 
3     |event1 |3 
4     |event3 |5 
4     |event4 |5

How i can get this table?(on column event{index} count sum) using only ability BigQuery(or SQL)

userId|event1 |event2|event3|event4
----------------------------------
1     |1      |2     |0     |0    |
2     |2      |1     |4     |0    |
3     |0      |0     |0     |0    |
4     |0      |0     |5     |5    |

Possible duplicate of [How to Pivot table in Big Query](http://stackoverflow.com/questions/26272514/how-to-pivot-table-in-big-query) — Jacob Lambert, Apr 17 '16 at 15:52
for example im have 50 unique events. Can u show how will be looked query? — ivan, Apr 17 '16 at 16:36

score 2 · Answer 1 · edited May 23 '17 at 12:01

2

If you have just few events below will work for you - you will need to construct as many respective rows as you have different events. If number of expected events constant - you can always easily build such query once and then use it

SELECT 
  userID,
  SUM(CASE WHEN event = 'event1' THEN [count] ELSE 0 END) AS event1,
  SUM(CASE WHEN event = 'event2' THEN [count] ELSE 0 END) AS event2,
  SUM(CASE WHEN event = 'event3' THEN [count] ELSE 0 END) AS event3,
  SUM(CASE WHEN event = 'event4' THEN [count] ELSE 0 END) AS event4
FROM YourTable
GROUP BY userId

If you need something more dynamic - look at very similar example https://stackoverflow.com/a/36623258/5221944

In your case that query to build dynamic sql will look as below

SELECT 'SELECT userId, ' + 
   GROUP_CONCAT_UNQUOTED(
      'SUM(IF(event="'+event+'",[count],0)) as [d_'+REPLACE(event,'/','_')+']'
   ) 
   + ' FROM YourTable GROUP BY userId ORDER BY userId'
FROM (
  SELECT event FROM YourTable GROUP BY event ORDER BY event
)

Note below line

      'SUM(IF(event="'+event+'",[count],0)) as [d_'+REPLACE(event,'/','_')+']'

It makes sure your even name complies with requirement for fields/columns name
If your evens will always look like event1, event2, etc you can simplify this line and use

      'SUM(IF(event = "' + event + '", [count], 0)) as ' + event

edited May 23 '17 at 12:01

Community

1
1

answered Apr 17 '16 at 16:43

Mikhail Berlyant

165,386
8
154
230

im read, but its very different way. What simple way to do this? For example in the R languege have function cast(). But how i can do this without R or python pandas – ivan Apr 17 '16 at 16:51
1

above is the best you can get if you want to do this within Web UI without using any scripting. If you willing to use any client of your choice - you anyway should follow the same path of first constracting dynamic query and then run it. NOTE: in current implementation of BigQuery - this is the only way. There is no any PIVOT specific function that would support this for you. Let's hope that in new BigQuery dialect - that Google Team is keep mentioning - this will be supported – Mikhail Berlyant Apr 17 '16 at 16:59
To set the expectations - upcoming updates to SQL dialect will have many improvement, but PIVOT and UNPIVOT are not part of it. – Mosha Pasumansky Apr 17 '16 at 17:34
Thank you Mosha for clearing this! Looking forward to play with new dialect! Excited! – Mikhail Berlyant Apr 17 '16 at 20:03

Big Query or SQL reshape data

1 Answers1