如何在PostgreSQL中转置列和行(即，如何切换行和列)? [英] How to transpose columns and rows in PostgreSQL (i.e., how do I switch rows and columns)?

问题描述

可能重复:
对sql结果进行转置，以便一列进入多列

Possible Duplicate:
Transposing an sql result so that one column goes onto multiple columns

我想在我的PSQL数据库中进行某种行/列交换.这是我的示例数据库:

I'd like to do a sort of row/column swapping in my PSQL database. Here's my example database:

id  place   price   year
1   U.S.    80  2000
2   U.S.    60  2001
3   U.S.    40  2002    
4   U.K.    600 2000
5   U.K.    500 2001
6   U.K.    350 2002

我想将该表转换为以下示例:

I would like to transform that table into the following example:

year    U.S.    U.K.
2000    80  600
2001    60  500
2002    40  350

在PostgreSQL中有可能吗?

Is this possible in PostgreSQL?

推荐答案

您可以使用聚合函数和CASE语句轻松完成此操作:

You can do this easily with an aggregate function and a CASE statement:

select year,
  sum(case when place = 'U.S.' then price else 0 end) "U.S.",
  sum(case when place = 'U.K.' then price else 0 end) "U.K."
from yourtable
group by year

请参见带演示的SQL小提琴

这篇关于如何在PostgreSQL中转置列和行(即，如何切换行和列)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！