Pivoting columns for a table with SQL (Snowflake)

Question

I have 3 tables that I need to join, with the endresult being a pivot of certain columns.

The situation is as follows:

Table 1

ID	COLUMN_A	COLUMN_B	COLUMN_C	COLUMN_G	NN_COLUMN_H	NN_COLUMN_I
1	10	1	Aa	123aa	null	null
1	20	2	Aa	56211	blah	null
1	30	3	Aa	123aa	vvvv	null
2	40	4	Bb	678cf	null	null
2	50	5	Bb	56211	blah	mmmm

Table 2

ID	COLUMN_C	COLUMN_J	NN_COLUMN_K
Aa_G	Aa	AaA	null
Aa_G	Bb	AaA	null
Aa_G	Cc	AaA	1234
Aa_G	Dd	AaA	5678

Table3

ID	COLUMN_D	COLUMN_E	COLUMN_F	COLUMN_L	NN_COLUMN_M	NN_COLUMN_N
1	VALUE1	10	01-03-2024	True	12	0000a
1	VALUE1	11	02-03-2024	True	34	0000b
1	VALUE2	10	01-03-2024	True	56	0000b
2	VALUE1	15	01-03-2024	True	78	0000b
2	VALUE2	12	01-03-2024	True	89	0000s
2	VALUE2	12	02-03-2024	True	45	00001

Table 4, the endresult

ID	VALUE1	VALUE2	COLUMN_F	COLUMN_C	COLUMN_G	COLUMN_J	COLUMN_L
1	10	10	01-03-2024	Aa	Aa_G	AaA	True
1	11	null	02-03-2024	Aa	Aa_G	AaA	True
2	15	12	01-03-2024	Bb	Aa_G	AaA	True
2	null	12	02-03-2024	Bb	Aa_G	AaA	True

I have tried the following:

SELECT * 
FROM
TABLE3
LEFT JOIN TABLE1
ON TABLE3.ID = TABLE1.ID
LEFT JOIN TABLE2
ON TABLE1.COLUMN_C = TABLE2.COLUMN_C
WHERE TABLE3.COLUMN_D in ('VALUE1', 'VALUE2')

However, I'm stuck as to where or how to implement the PIVOT line. The columns that should be pivoted are column_D and column_E in table3.

Answer 1

See example

select id
  ,max(case when column_d='VALUE1' then column_e end)value1
  ,max(case when column_d='VALUE2' then column_e end)value2
  ,column_f
from table3
group by id,column_f
order by id,column_f;

Case when more than 1 (Id,VALUEx) per date

select id
  ,max(case when column_d='VALUE1' then column_e end)value1
  ,max(case when column_d='VALUE2' then column_e end)value2
  ,column_f,rn
from (
  select *
    ,row_number()over(partition by id,column_f,column_d order by column_f)rn
  from table3
)t
group by id,column_f,rn
order by id,column_f,rn;

Join as

select *
from(
  select id
    ,max(case when column_d='VALUE1' then column_e end)value1
    ,max(case when column_d='VALUE2' then column_e end)value2
    ,column_f
  from table3
  WHERE TABLE3.COLUMN_D in ('VALUE1', 'VALUE2')
  group by id,column_f
)t3
LEFT JOIN TABLE1
ON T3.ID = TABLE1.ID
LEFT JOIN TABLE2
ON TABLE1.COLUMN_C = TABLE2.COLUMN_C
order by t3.id,t3.column_f;

Fiddle