Transpose columns in rows using BigQuery sql

user_14648908 0
Viewed 2372

Here is my table :

Ligne  | registration_terms | registration_code_phone | registration_name | registration_email | registration_password | registration_password_nextbutton | R1_ended | date
-------+--------------------+-------------------------+-------------------+--------------------+-----------------------+----------------------------------+----------+-------------------------
1      |                 84 |                      78 |                75 |                 74 |                    72 |                               68 |       67 | 2020-11-13 00:00:00 UTC
2      |                 96 |                      91 |                84 |                 83 |                    81 |                               77 |       77 | 2020-11-07 00:00:00 UTC
3      |                123 |                     118 |               116 |                115 |                   108 |                              107 |      106 | 2020-11-08 00:00:00 UTC
4      |                108 |                      98 |                87 |                 84 |                    75 |                               73 |       73 | 2020-11-09 00:00:00 UTC
5      |                 79 |                      77 |                67 |                 64 |                    59 |                               56 |       55 | 2020-11-10 00:00:00 UTC
6      |                 82 |                      77 |                69 |                 65 |                    61 |                               59 |       59 | 2020-11-12 00:00:00 UTC
7      |                 66 |                      63 |                57 |                 55 |                    49 |                               49 |       49 | 2020-11-11 00:00:00 UTC
8      |                 60 |                      59 |                54 |                 49 |                    44 |                               43 |       43 | 2020-11-14 00:00:00 UTC

I would like to transpose my columns names into rows so that I have 3 columns :

  • one with 'value_names' with columns names in each row
  • one with 'values'
  • one with 'date'

I'm using BigQuery

google-bigquery transpose pivot sql
1 Answers

Below is for BigQuery Standard SQL

#standardSQL
select date, 
  split(kv, ':')[offset(0)] as value_names,
  split(kv, ':')[offset(1)] as values
from `project.dataset.table` t, 
unnest(split(translate(to_json_string(t), '"{}', ''))) kv
where split(kv, ':')[offset(0)] != 'date'

if to apply to sample data from your question - output is

enter image description here

user_52219440
Related