Reformat data with Pivot or UnPivot
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.
What have you tried? This looks as though it should be a relatively straightforward UNPIVOT - having said that, I don't have a SQL Server installation to test against right now...
Also, do you have a script to provide some sample data that we could use for testing?
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;
