2

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?

Community
  • 1
  • 1
user1626730
  • 3,783
  • 5
  • 20
  • 24
  • You'll find lots of results, not necessarily postgres-related, if you search for the term [tag:pivot]. Note that Oracle and SQL Server actually implement a `PIVOT` clause, but not Postgres – Lukas Eder Oct 29 '12 at 19:06
  • 3
    Check out the crosstab functions in the tablefunc module: http://www.postgresql.org/docs/current/static/tablefunc.html –  Oct 29 '12 at 21:55
  • See: https://github.com/jumpstarter-io/colpivot for a dynamic solution. – Hannes Landeholm Oct 19 '15 at 01:18

2 Answers2

8

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

Taryn
  • 242,637
  • 56
  • 362
  • 405
0

This is called a "pivot", and there's no special syntax in postgres to support it - you have to code it using SQL, for example:

select
    year,
    us.price as us,
    uk.price as uk
from mytable us
left join mytable uk on us.year = uk.year
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 1
    do that with 100 columns and you're doomed … see http://en.wikipedia.org/wiki/Relational_algebra#Natural_join_.28.E2.8B.88.29 – boecko Nov 06 '12 at 14:37