How to get multiple value in one row in postgreSQL?

Question

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

jarlh · Accepted Answer · 2019-01-02 10:31:26Z

7

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”.)

edited Jan 2, 2019 at 10:31

answered Jan 2, 2019 at 10:24

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

Comments

Fahmi · Accepted Answer · 2019-01-02 10:18:32Z

3

You can try below - using conditional aggregation

select emp_name, 
  max(case when leave_type='PL' then total_lday end) as paid_leave,
  max(case when leave_type='UL' then total_lday end) as unpaid_leave
from emp_leave
group by emp_name

answered Jan 2, 2019 at 10:18

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to get multiple value in one row in postgreSQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related