如何正确地将 SQL 行转换为列? [英] How to correctly convert SQL rows to columns?

问题描述

在有人提到它之前，我在 Stack 上看到过完全相同的 SQL 问题，从我的角度来看，这实际上是将列转换为行.我想要完成的事情如下图所示，考虑到顶部，我想创建一个新表，其中包含从这个意义上翻转的先前数据.

Before someone mentions it, I have seen the exact same SQL question on Stack before and from my point of view, that's actually transposing columns to rows. What I'm trying to accomplish is as seen below in the photos, given the top one, I want to create a new table with the previous data that is flipped in this sense.

推荐答案

你可以使用条件聚合和union all如下:

You can use the conditional aggregation and union all as follows:

select name_new, 
       max(case when name = 'PersonA' then A end) as PersonA,
       max(case when name = 'PersonB' then A end) as PersonB,
       max(case when name = 'PersonC' then A end) as PersonC
from 
(select name, 'A1' name_new, A1 A from mytable union all
select name, 'A2' name_new, A2 A from mytable union all
select name, 'A3' name_new, A3 A from mytable union all
select name, 'A4' name_new, A4 A from mytable ) t
group by name_new 

SQLFiddle

这篇关于如何正确地将 SQL 行转换为列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！