Transposing a row data in multiple rows based on multiple columns I have data in the database in the below format



And I want output data in this format

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




Basically timestamp in the desired result is '01-' + &lt;'MONTH'&gt; + &lt;'YEAR'&gt;

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)
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.