sqlpostgresql-9.4

How to get multiple value in one row in postgreSQL?

I have database table name emp_leave in postgreSQL9.4 like

emp_name leave_type total_lday
Tame PL 3
Tame UL 4

I want the query result like

emp_name paid_leave unpaid_leave
Tame 3 4
Solution

  • Postgresql 9.4 also has the FILTER clause to do conditional aggregation:

    select emp_name, 
       max(total_lday) FILTER (where leave_type = 'PL') as paid_leave,
       max(total_lday) FILTER (where leave_type = 'UL') as unpaid_leave
from emp_leave
group by emp_name

    (ANSI SQL-2016, Feature T612, “Advanced OLAP operations”.)