CODEWITHSUNDEEP
google-bigquery
Vikash Chandra
Vikash Chandra

Reputation: 1

Conversion of columns to rows

ID Name     M   E   H   S
1   Sally   78  85  91  76
2   Edward  87  90  82  87

convert to

ID Name Subject Marks
1  Sally M       78
1  Sally E       85
1  Sally H       91
1  Sally S       76
2  Edward M       87
2  Edward E       90
2 Edward H       82
2 Edward S       87

Upvotes: 0

Views: 1381

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

In many practical case number of columns are big enough to enlist in the query or even unknown in advance - so below approach is covering more generic cases - you don't need to know in advance number and name of columns

select id, name, key, value
from your_table t,
unnest([to_json_string((select as struct * except(id, name) from unnest([t])))]) json,
unnest(bqutil.fn.json_extract_keys(json)) key with offset 
join unnest(bqutil.fn.json_extract_values(json)) value with offset 
using (offset)

if applied to sample data in your question - output is

enter image description here

Upvotes: 0

Daniel Zagales
Daniel Zagales

Reputation: 3034

The unpivot function will perform the action you're looking for, try the following:

with sample_data as (
    SELECT 1 as id,   'Sally' as name,   78 as M,  85 as E, 91 as H,  76 as S UNION ALL
    SELECT 2,   'Edward',  87,  90, 82,  87
)

SELECT id, name, subject, marks
from sample_data
unpivot(marks for subject in (M,E,H,S));

for more information on unpivot see the docs here: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unpivot_operator

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

One simple approach uses a series of unions:

SELECT ID, Name, 'M' AS Subject, M AS Marks, 1 AS pos FROM yourTable UNION ALL
SELECT ID, Name, 'E', E, 2 FROM yourTable UNION ALL
SELECT ID, Name, 'H', H, 3 FROM yourTable UNION ALL
SELECT ID, Name, 'S', S, 4 FROM yourTable
ORDER BY ID, pos;

Upvotes: 0

Related Questions