将列转换为行

Question

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

转换成

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

Answer 1

Un枢轴函数将执行您要寻找的操作，请尝试以下操作：

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));

有关不枢轴的更多信息，请参见此处的文档：运算符

Answer 2

一种简单的方法使用了一系列的联合：

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;

Answer 3

在许多实际情况下，列的数量足够大，可以在查询中登记，甚至事先不知道--因此下面的方法涵盖了更一般的情况--您不需要预先知道列的编号和名称。

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)

如果应用于问题中的样本数据，则输出为

​

​