How to display value correctly between 2 tables

Question

I have two tables that I'm trying to show a flat view from. I have table A with a Product Code and Table B with that Code and a column of Status codes (Done, NotDone, InProcess). That status has a date/time associated with its product code. When I join on the code, I get all the codes regardless if they have a datetime or not. How do I display Product Code, DoneDateTime, InProcessDateTime, NotDoneDateTime in their own columns as a single flat view assuming they datetime code exists? Table A

ProdCode
111
222
223

TableB

ProdCode	Status	Date
111	Done	1/10/01
111	Start	1/1/01
222	Done
222	Start	1/2/01

Desired View

ProdCode	Start Date	Done Date
111	1/1/01	1/10/01
222	1/2/01

Answer 1

You can use conditional aggregation as one method. I think it's better to show all prodCodes, even those which haven't started, hence the left join. Or would you not want to see prodCode 223? If not, then change LEFT to INNER.

select 
 a.prodCode, 
 max(case when b.status = 'Start' then Date end) as start_date, 
 max(case when b.status = 'Done' then Date end) as end_date
from 
 tableA a
left join 
 tableB b
  on a.prodCode = b.prodCode
group by a.prodCode
order by 1

prodCode	start_date	end_date
111	2001-01-01	2001-01-10
222	2001-01-02	null
223	null	null

fiddle