Postgres rows to columns

Question

I have read a lot of questions on how to "transpose" or pivot data i Postgres but nothing has given me enough to see if the thing I am trying to do is even possible.

Given a set of data in a Postgres table like this:

issue       valid       station     value
2013-02-01  2013-02-01  A           1
2013-02-01  2013-02-02  A           1.2
2013-02-01  2013-02-03  A           1.3
2013-02-01  2013-02-04  A           1.0
2013-02-01  2013-02-01  B           2.1
2013-02-01  2013-02-02  B           2.1
2013-02-01  2013-02-03  B           2.4
2013-02-01  2013-02-04  B           2.7
2013-02-01  2013-02-01  C           3.2
2013-02-01  2013-02-02  C           3.7
2013-02-01  2013-02-03  C           3.5
2013-02-01  2013-02-04  C           3.5

I would like to be able make a query that if I set issue=2013-02-01 results in:

station  val_day1  day1        val_day1  day2        val_day3  day3        val_day4  day4        max_val
A        1         2013-02-01  1.2       2013-02-02  1.3       2013-02-03  1.0       2013-02-04  1.3     
B        2.1       2013-02-01  2.1       2013-02-02  2.4       2013-02-03  2.7       2013-02-04  2.7 
C        3.2       2013-02-01  3.7       2013-02-02  3.5       2013-02-03  3.5       2013-02-04  3.7

Unfortunately I am very limited in what I can change so if this is possible to solve in a sql query that would be awesome.

The data has always 4 different days as "valid" for each issue. A new issue is added every day.

Is this even possible to try to get going in Postgres?

Thanks

Frank Schmitt · Accepted Answer · 2014-02-07 11:45:05Z

1

You can pivot your data using a combination of CASE and MAX:

select v2.station, 
  max(day1) as day1,
  max(val_day1) as val_day1,
  max(day2) as day2,
  max(val_day2) as val_day2,
  max(day3) as day3,
  max(val_day3) as val_day3,
  max(day4) as day4,
  max(val_day4) as val_day4,
  max(greatest(val_day1, val_day2, val_day3, val_day4)) as max_val
from (
select v1.*, 
  (case when v1.day = 1 then v1.valid else NULL end) as day1,
  (case when v1.day = 1 then v1.value else NULL end) as val_day1,
  (case when v1.day = 2 then v1.valid else NULL end) as day2,
  (case when v1.day = 2 then v1.value else NULL end) as val_day2,
  (case when v1.day = 3 then v1.valid else NULL end) as day3,
  (case when v1.day = 3 then v1.value else NULL end) as val_day3,
  (case when v1.day = 4 then v1.valid else NULL end) as day4,
  (case when v1.day = 4 then v1.value else NULL end) as val_day4
from
(select t.valid, t.station, t.value, t.valid - t.issue +1 as day
from so_data t) v1
  ) v2
group by v2.station
order by v2.station

SQL Fiddle

answered Feb 7, 2014 at 11:45

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jesper Over a year ago

Thank you! Works great. I really need to look more into case. Seems very powerful

Craig Ringer Over a year ago

For a more general purpose solution, read the "crosstab" and "pivot" tags, and look into the PostgreSQL "crosstab" function in the "Tablefunc" extension.

Collectives™ on Stack Overflow

Postgres rows to columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related