convert range to database

There may not be a good solution for this request. The objective is to convert Sheet 1 (Vertically arranged data good for analysis) to look like Sheet 2 (Merged cells, different header names, fewer records, etc.). Typically, the request would be the opposite, make Sheet 2 looks like Sheet 1. I understand different systems require data to be arranged in unorthodox ways to make sheet import-ready.

If I was to take on this project, I'd use a pivot table to re-shape the data in Sheet 1 and fix the rest manually. I don't think Power Query or vba are of any use here.