BigQuery SQL -如何将前3项结果转换为列
BigQuery SQL -如何将前3项结果转换为列
提问于 2022-06-27 21:39:49
长期潜伏的第一次海报..。
我有一张像下面这样的平坦的桌子，我想按事件把前三名的选手带回到不同的列中。
with races as (
select "200M" as race, "johnson" as name,23.5 as finishtime
union all
select "200M" as race, "smith" as name,24.1 as finishtime
union all
select "200M" as race, "anderson" as name,23.9 as finishtime
union all
select "200M" as race, "jackson" as name,24.9 as finishtime
union all
select "400M" as race, "johnson" as name,47.1 as finishtime
union all
select "400M" as race, "alexander" as name,46.9 as finishtime
union all
select "400M" as race, "wise" as name,47.2 as finishtime
union all
select "400M" as race, "thompson" as name,46.8 as finishtime
)
select * from races我希望输出基本上如下所示：
Race | 1st Place | 2nd Place | 3rd Place
200M | johnson | anderson | smith
400M | thompson | alexander | johnson我没有在上面的数据中打成平局，但我也会有一些.
提前感谢！
回答 1
Stack Overflow用户
发布于 2022-06-27 21:46:11
考虑以下几点
select * from (
select race, name,
row_number() over(partition by race order by finishtime) pos
from races
)
pivot (any_value(name) as place for pos in (1,2,3))如果应用于问题中的样本数据，则输出为
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72778572
复制相关文章
相似问题
腾讯云开发者
