Search code examples
sqlsql-servert-sqlunpivot

Unpivot monthly plan data

How can I transform this input

Product | CC | 2017_11 | 2017_12
Product X | 220 | 100 | 200

into something like this?

Product | CC | Month |EUR
Product X | 220 | 2017_11 | 100
Product X | 220 | 2017_12 | 200

I tried it with UNPIVOT but was not able to get the period headers into rows. Here is my sample

    SELECT Product, [month] FROM 

(SELECT Product
            [2017_11],
            [2017_12]
 FROM
[MONTHLY_REPORTING].[dbo].[FP_2017_2018_V2_revenue_import]

)
as input

UNPIVOT 
    ([month] FOR month_x IN ([2017_11], [2017_12])) as U1
Solution

  • You need to UNPIVOT the value, in your case EUR, for month in your headers

     SELECT Product, [month], [EUR] 
FROM (
    SELECT
        Product
        ,[2017_11]
        ,[2017_12]
     FROM [MONTHLY_REPORTING].[dbo].[FP_2017_2018_V2_revenue_import]
) as input
UNPIVOT (
    [EUR] FOR [month] IN ([2017_11], [2017_12])
) as U1