-3

I'm struggling trying to covert the format of a table with the following fields:

Customer|Fiscal Year| September Amount| October Amount|November Amount|...

to:

Customer|Fiscal Year|Month|Amount

I've tried pivot and unpivot but I've not been successful.

Thanks!

0
0

This is an unpivot:

SELECT
    U.Customer,
    U.[Fiscal Year],
    [Month] = LEFT(U.[Month], CHARINDEX(SPACE(1), U.[Month]) - 1),
    U.Amount
FROM #Test AS T
UNPIVOT 
(
    Amount 
    FOR [Month] IN 
    (
        [September Amount], 
        [October Amount], 
        [November Amount]
    )
) AS U;

or

SELECT
    T.Customer,
    T.[Fiscal Year],
    U.[Month],
    U.Amount
FROM #Test AS T
CROSS APPLY 
(
    VALUES
        ('September', T.[September Amount]),
        ('October', T.[October Amount]),
        ('November', T.[November Amount])
) AS U ([Month], Amount);

db<>fiddle demo

Not the answer you're looking for? Browse other questions tagged or ask your own question.