Pivot data row to header using SQL in Postgresql

Question

I get this result using SQL request:

NBR_TYPE         Count
-----------------------
0021              20
0022              19
0023              13

But i want result like this :

0021           0022         0023
---------------------------------
20              19          13

Can anyone help me to achieve this using PosgreSQL and thanks.

GMB · Accepted Answer · 2020-04-20 22:35:35Z

2

If you have a predefined list of values, you can use conditional aggregation to pivot:

select
    max(count) filter(where nbr_type = '0021') "0021",
    max(count) filter(where nbr_type = '0022') "0022",
    max(count) filter(where nbr_type = '0023') "0023"
from mytable

This assumes that each nbr_type appears in just one row; otherwise, you possibly want to change the max() to a more meaningful aggregation function, such as sum() or avg().

answered Apr 20, 2020 at 22:35

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

GMB Over a year ago

@A.khalifa: nope, this is Postgres syntax. In Oracle, you would do: max(case when nbr_type = '0021' then count end) "0021" and so on. As for the title of your question: the term is pivot (which I tagged your question with too) rather than transfer.

Collectives™ on Stack Overflow

Pivot data row to header using SQL in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related