question

ajd3rd avatar image
ajd3rd asked

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.

pivotsql-server-2016unpivot
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?

0 Likes 0 ·

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered

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;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.