How to convert a column to a row in Google big query

Question

I am trying to convert a column to the row, because I would like to see the funnel of people that go through our events and one event is with parameter.

I have tried to select all the data but I got to the point where I have selected the desired data as column, instead of a row and as one record.

SELECT event_name, 
       (SELECT count(distinct(user_pseudo_id))
       FROM `dataset` as t2, UNNEST(event_params) as p
       WHERE t2.event_name = "event_name1" and p.value.string_value IN ("param1","param2","param3","param4")
       ),
       count(distinct(user_pseudo_id))
FROM `dataset` as t1
WHERE event_name IN ("event_name1",
"event_name2",
"event_name3") AND
GROUP BY 1
ORDER BY 3 DESC

I am receiving this result:

event_names    f0       f1  
event_name1    1600     2000
event_name2    1600     1400
event_name3    1600     1200

This is my desired output:

event_names           f1    
event_name1           2000
event_name1_param     1600     -> the result from f0
event_name2           1400
event_name3           1200

Perhaps this? https://stackoverflow.com/a/40766540/4303418 – Eben du Toit Apr 03 '19 at 17:32 — Eben du Toit, Apr 03 '19 at 17:32

score 0 · Accepted Answer · answered Apr 08 '19 at 14:11

Solved by union all.

SELECT event_name,
                 count(distinct user_pseudo_id)
FROM `dataset` as t1
WHERE event_name IN ("event_name1","event_name2","event_name3")

union all

SELECT event_name, --případně jako "event_name1_param"
                  count(distinct user_pseudo_id)
FROM `dataset` as t2, UNNEST(event_params) as p
WHERE t2.event_name = "event_name1"
and p.value.string_value IN ("param1","param2","param3","param4")

How to convert a column to a row in Google big query

1 Answers1