How to transpose columns and rows in PostgreSQL (i.e., how do I switch rows and columns)?

pivot, postgresql, sql

Solution

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

See SQL Fiddle with Demo

Problem

Possible Duplicate: Transposing an sql result so that one column goes onto multiple columns 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 ``` Is this possible in PostgreSQL?

Original source

Related problems