transpose single row to column

Question

I have seen similar questions but the answers did not work for me, I would like to transpose the following table :

| 2017  | 2018  | 2019  | 2020  |
|------ |------ |------ |------ |
| 358   | 1300  | 973   | 101   |

And would like to get this table :

|       | Count     |
|------ |-------    |
| 2017  | 358       |
| 2018  | 1300      |
| 2019  | 973       |
| 2020  | 101       |

Does this answer your question: https://stackoverflow.com/questions/23060256/postgres-transpose-rows-to-columns — MGP, Nov 17 '20 at 15:17

a_horse_with_no_name · Answer 1 · 2020-11-17T15:31:41.433

1

Use a lateral join:

select x.year, x.count
from the_table t
  cross join lateral (
      values ("2017", 2017), ("2018", 2018), ("2019", 2019), ("2020", 2020)
  ) as x(count, year)

Online example

edited Nov 17 '20 at 15:31

answered Nov 17 '20 at 15:17

a_horse_with_no_name

497,550
91
775
843

It does not work for me. I got the error ERROR: column "2017" does not exist in t – enriqueqs Nov 17 '20 at 15:27
1

@enriqueqs: well your sample data shows a column named `"2017"` - if you don't have that you should have shown us the **real** column names. – a_horse_with_no_name Nov 17 '20 at 15:31
it came from `sum(2017_values) as "2017" ` – enriqueqs Nov 17 '20 at 15:36
1

@enriqueqs: please edit your question and show us the complete query. With the current information in the question, this is the only answer that is possible to give. – a_horse_with_no_name Nov 17 '20 at 15:40
1

@enriqueqs . . . This answers the question that you have asked, and I think you should accept the answer. If you have a *different* question, then ask it as a *new* question. – Gordon Linoff Nov 17 '20 at 15:49

transpose single row to column

1 Answers1