Postgres convert values to columns

Question

I have postgres table with structure:

|key| position |    date    |
|---|----------|------------|
| 1 |     5    | 2017-07-01 |
|---|----------|------------|
| 1 |     9    | 2017-07-02 |
|---|----------|------------|
| 2 |     4    | 2017-07-01 |
|---|----------|------------|
| 2 |     8    | 2017-07-02 |

But I need to have the selected data in a format like this:

| key | 2017-07-01 | 2017-07-02 |
|-----|------------|------------|
|  1  |      5     |      9     |
|-----|------------|------------|
|  2  |      4     |      8     |

How can I do something like this?

https://www.postgresql.org/docs/current/static/tablefunc.html please look here — Vao Tsun, Aug 04 '17 at 11:12
Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Durgpal Singh, Aug 04 '17 at 11:20
Possible duplicate of [Pivot Row on PostgreSQL](https://stackoverflow.com/questions/41494299/pivot-row-on-postgresql) — Ulrich Thomas Gabor, Aug 04 '17 at 11:37

score 1 · Accepted Answer · answered Aug 04 '17 at 11:49

1

If you have one row per key and per date, then one way is conditional aggregation

select 
key,
min(case when date = '2017-07-01' then position end) as "2017-07-01",
min(case when date = '2017-07-02' then position end) as "2017-07-02"
from t
group by key

answered Aug 04 '17 at 11:49

Oto Shavadze

40,603
55
152
236

thants, that's what I need – Dmytro Kotenko Aug 04 '17 at 11:59

Postgres convert values to columns

1 Answers1