Write columns as rows (PostgreSQL)

I have a table:

campaign_id	media	installs	content_view	date	unique_purchase
7564	google	613	45	2021-09-01	1
7564	facebook	21	33	2021-09-02	2
3451	google	83	12	2021-09-01	0

I want to create view:

• write installs column values as metric_key=installs, metric_name=installs, metric_value=...
• write content_view and unique_purchase as metric_key=event, metric_name=column_name, metric_value=...

id	media_source	metric_key	metric_name	metric_value	date
7564	google	installs	installs	613	2021-09-01
7564	facebook	installs	installs	21	2021-09-02
3451	google	installs	installs	83	2021-09-01
7564	google	event	unique_purchase	1	2021-09-01
7564	facebook	event	unique_purchase	2	2021-09-02
3451	google	event	unique_purchase	0	2021-09-01
7564	google	event	content_view	45	2021-09-01
7564	facebook	event	content_view	33	2021-09-02
3451	google	event	content_view	12	2021-09-01

Also it should be groupped by campaign_id, media, date.

How to achieve it with Postgres? I dont understand crosstab clearly. Thanks!

Answer

Use a lateral join:

select t.id, t.media_source, v.*, t.date
from t cross join lateral
     (values ('installs', t.installs, 'installs'),
             ('event', t.unique_purchase, 'unique_purchase'),
             ('event', t.content_view, 'content_view')
     ) v(metric_key, metric_name, metric_value);