Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ctrlr_err_hist (
    visit_id int,
    err_cd int,
    err_name text,
    m1_err_cnt int
);

INSERT INTO ctrlr_err_hist VALUES
(1, 0, 'encoder', 500),
(1, 3, 'breakout', 212),
(2, 1, 'obclose', 45),
(2, 3, 'breakout', 143);

SELECT * FROM ctrlr_err_hist;
4 rows affected



visit_id
err_cd
err_name
m1_err_cnt


1
0
encoder
500

1
3
breakout
212

2
1
obclose
45

2
3
breakout
143


CREATE TABLE tchn_visit_hist (
    visit_id int,
    door_id int
);

INSERT INTO tchn_visit_hist VALUES
(1, 10), (1, 20), (2, 42);
3 rows affected

SELECT
    visit_id,
    MIN(m1_err_cnt) FILTER (WHERE err_cd = 0) as encoder_cnt,
    MIN(m1_err_cnt) FILTER (WHERE err_cd = 1) as ob_close_cnt,
    MIN(m1_err_cnt) FILTER (WHERE err_cd = 3) as breakout_cnt
FROM
    ctrlr_err_hist
GROUP BY visit_id
    


visit_id
encoder_cnt
ob_close_cnt
breakout_cnt


1
500
null
212

2
null
45
143


SELECT t.visit_id, t.door_id, c.encoder_cnt, c.breakout_cnt, c.ob_close_cnt
FROM (
   SELECT
       visit_id,
       MIN(m1_err_cnt) FILTER (WHERE err_cd = 0) as encoder_cnt,
       MIN(m1_err_cnt) FILTER (WHERE err_cd = 1) as ob_close_cnt,
       MIN(m1_err_cnt) FILTER (WHERE err_cd = 3) as breakout_cnt
   FROM
       ctrlr_err_hist
   GROUP BY visit_id
) c
JOIN tchn_visit_hist t
ON (t.visit_id = c.visit_id)


visit_id
door_id
encoder_cnt
breakout_cnt
ob_close_cnt


1
10
500
212
null

1
20
500
212
null

2
42
null
143
45


--- Your solution as comparision
select t.visit_id, t.door_id, enc.encoder_cnt, brk.breakout_cnt, ob.ob_close_cnt
from tchn_visit_hist t
left join (
    select m1_err_cnt as encoder_cnt, visit_id
    from ctrlr_err_hist  
    where err_cd = '0' 
) as enc 
on t.visit_id = enc.visit_id
left join (
    select m1_err_cnt as breakout_cnt, visit_id
    from ctrlr_err_hist  
    where err_cd = '3'
) as brk 
on t.visit_id = brk.visit_id
left join (
    select m1_err_cnt as ob_close_cnt, visit_id
    from ctrlr_err_hist  
    where err_cd = '1'
) as ob 
on t.visit_id = ob.visit_id


visit_id
door_id
encoder_cnt
breakout_cnt
ob_close_cnt


1
10
500
212
null

1
20
500
212
null

2
42
null
143
45