PostgreSQL Query Using Crosstab

Question

Now, I am leaning postgreSQL. In the study, I found crosstab in postgreSQL. I tried to apply this function to my customized table, but it dose not work. please help!!

This is my Table

 year | type     | count 
------+----------+----
 2015 | AS       |  6
 2015 | HY       |  6
 2015 | KR       |  6
 2015 | SE       |  6
 2016 | AS       |  2
 2016 | HY       |  2
 2016 | KR       |  2
 2016 | SE       |  2
 2017 | AS       |  1
 2017 | HY       |  1
 2017 | KR       |  1
 2017 | SE       |  1
 2018 | AS       |  2
 2018 | HY       |  2
 2018 | KR       |  2
 2018 | SE       |  2

I want to change this table like this

year |  AS  |  HY  |  KR  |  SE  |
----------------------------------
2015 |   6  |   6  |   6  |   6  |
2016 |   2  |   2  |   2  |   2  |
2017 |   1  |   1  |   1  |   1  |
2018 |   2  |   2  |   2  |   2  |

To make that table, I designed query using crosstab, but dose not work!

Please Let me know the query of this problem.

Please always check for formatting while posting the question. It can be seen below the input field. I’ve fixed it for you now. Also what kind of queries did you try that didn’t work? — Sami Kuhmonen, Dec 27 '18 at 08:50

score 0 · Answer 1 · answered Dec 27 '18 at 09:32

You could achieve this without Crosstab, you can use Aggregate function.

Query :

select 
  year,
  max(counts) filter (where type = 'AS') as "AS",
  max(counts) filter (where type = 'HY') as "HY",
  max(counts) filter (where type = 'KR') as "KR",
  max(counts) filter (where type = 'SE') as "SE"
from
  tbl
group by
  year
order by 
  year asc

Demo <> DB Fiddle

And if you are try learning Crosstab this answer are really great and explain really well about to do pivot use Crosstab.

PostgreSQL Crosstab Query

PostgreSQL Query Using Crosstab

1 Answers1