对不同列使用秩

我只需要使用条件聚合:

select city,
       max(case when rank = 1 then topic end) as top_1,
       max(case when rank = 2 then topic end) as top_2,
       . . . 
       max(case when rank = 10 then topic end) as top_10
from t
group by city;

rank 没有关系。如果你有领带,你可能更喜欢 listagg() 而不是 max() .

你可以试试

SELECT * FROM
(
SELECT City,Topic, 
 'Top '+ cast(Rank as varchar(10)) AS TopRank
 FROM TableName
 ) T
PIVOT 
(
max(topic) 
FOR TopRank IN ([Top 1], [Top 2],[Top 10])
) as pvt

MySQL中没有直接的Pivot up。 您需要在查询中手动创建列。

select City, max(case when Rank=1 then TopiC end) as Top1, max(case when Rank=2 then Topic end) as Top2, . . . max(case when Rank=10 then Topic end) as Top10 from Table group by City