Transposing a row data in multiple rows based on multiple columns

Question

I have data in the database in the below format

enter image description here

And I want output data in this format

BudgetCategoryId | CostCenterDepartmentId | CostCenter | AccountNo | Amount | Timestamp
714 | 135 | 12010194 | 110 | 102000.00 | 01-01-2020 

enter image description here

Basically timestamp in the desired result is '01-' + <'MONTH'> + <'YEAR'>

I tried transposing, pivot, unpivot but unfortunately unable to get the desired result.

Column Names:

Id | BudgetCategoryId | CostCenterDepartmentId | Year | AccountNo | CostCenter | Month1 | Month2 | Month3 | Month4 | Month5 | Month6 | Month7 | Month8 | Month9 | Month10 | Month11 | Month12 | Budget 

PS: I am very new in SQL (RDBMS)

Answer 1

You probably need to do it in several steps: first unpivot, then calculate the timestamp.

Unpivoting would be

SELECT BudgetCategoryId, CostCenterDepartmentId, CostCenter, AccountNo, Year, Amount, Month
 FROM (
 SELECT BudgetCategoryId, CostCenterDepartmentId, CostCenter, AccountNo, Year,
 Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12
 FROM table) p
 UNPIVOT (
 Amount FOR Month IN (Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12)
)

The timestamp can be calculated as DATEFROMPARTS(Year, CAST(SUBSTRING(Month, 6, LEN(Month) - 5)), 1) and it might work to add it as an extra field in the above SELECT, or you might need to do an extra SELECT on the above result.