I have a table with the following columns:
Customer, fiscal year, September, October....all of the months.
1 2019 $500.00 $200.00 .....
I'm trying to change the format to
Customer, fiscal year, month name, amount.
1 2019 September $500
1 2019 October $200 ....
I've tried pivot and unpivot but I'm unable to get it into the proper format.
Any help would be greatly appreciated.
Answer by ThomasRushton ·
This seems to work, albeit with very limited data.
DECLARE @testdata TABLE (CustomerID INT, FinancialYear INT, September MONEY, October MONEY); INSERT INTO @testdata VALUES (1, 2019, 500, 200); SELECT * FROM @testdata; SELECT CustomerID, FinancialYear, NameOfMonth, amount FROM (SELECT CustomerID, FinancialYear, September, October FROM @testdata) p UNPIVOT (amount FOR NameOfMonth IN (September, October) ) AS u;