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-' + <'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)
1 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.