Separate 1 tables into two based on conditions and then JOIN the two tables keeping NULL instead of duplicating

Question

I have a "Customer History" table which holds different type of entries.

I want to get entries with Type = 'E-mail' and Source = 'External' or Source = 'Customer'.

So I would get:

Id	Type	Source	Date
11	E-mail	External	12/02/24
11	E-mail	External	13/02/24
11	E-mail	Customer	10/02/24

MY MAIN GOAL is to then modify this result to get:

Id	External source Date	Customer Source Date
11	12/02/24	10/02/24
11	13/02/24	NULL

I just need to have to date columns, one to be taking dates only from 'E-mail' entries with Source - 'External', and one taking dates when source - 'Customer'

I have tried all different JOINs, I have applied WHERE conditions, adding conditions to the ON clause in the JOIN itself, read different posts in here and in Google, and I can't make it work.

Answer 1

This can be done using window function row_number() to assign a unique identifier to each row according to its source, then apply the conditional aggregation using the aggregate function MAX() :

select Id,
       max(case when Source = 'External' then Date end) as 'External source Date',
       max(case when Source = 'Customer' then Date end) as 'Customer source Date'
from (
  select *, row_number() over (partition by Id, Type, Source order by Date) as rn
  from mytable
) as s
group by Id, Type, rn;

Results :

Id  External source Date    Customer source Date
11  2024-02-12              2024-02-10
11  2024-02-13              null

Demo here