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 :
I'm using BigQuery
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