Search code examples
sqlsql-server-2008unpivot

Unpivot Data with Multiple Columns - Syntax Help Please

I have the following data in which I would like to unpivot

enter image description here

I created this query to unpivot the 'Actual' rows but can't seem to figure out the syntax for unpivoting the 'Plan' and 'PriorYear' as well.

SELECT FiscalYear, Period, MetricName, ActualValue
FROM vw_ExecSummary
UNPIVOT
(ActualValue FOR MetricName IN ( [Net Revenue], [Total C.S. Salaries]
)) AS unpvt  
WHERE [Type] = 'Actual'

The unpivoted data looks like this but I want to also add the Plan and PriorYear columns to the right of the ActualValue column below

enter image description here

Any help would be greatly appreciated. Thanks.

Solution

  • I can't test this at the moment, but I think it works like this. First, you need to UNPIVOT all the data:

    SELECT fiscalYear, period, type, metricName, metricValue
FROM vw_ExecSummary
UNPIVOT (metricValue FOR metricName IN ([Net Revenue], [Total C.S. Salaries])) unpvt

    Which should result in a table that looks something like this:

    fiscalYear  period  type      metricName             metricValue
===================================================================
15          1       'Actual'  'Net Revenue'          3676798.98999997
15          1       'Actual'  'Total C.S. Salaries'  1463044.72
15          1       'Plan'    'Net Revenue'          3503920.077405
...................... (remaining rows omitted)

    We could then PIVOT the rows as normal to get the new columns (that's what it's for):

    SELECT fiscalYear, period, metricName, 
       [Actual] AS actualValue, [Plan] AS planValue, [PriorYear] AS priorYearValue
FROM <previous_data>
PIVOT (SUM(metricValue) FOR (type IN ([Actual], [Plan], [PriorYear]) pvt

    (the SUM(...) shouldn't actually do anything here, as presumable the other columns comprise a unique row, but we're required to use an aggregate function)

    ...which should yield something akin to the following:

    fiscalYear  period  metricName     actualValue       planValue       priorYearValue
======================================================================================
15          1       'Net Revenue'  3676798.98999997  3503920.077405  40436344.4499999
...................................... (remaining rows omitted)

    So putting it together would look like this:

    SELECT fiscalYear, period, metricName, 
       [Actual] AS actualValue, [Plan] AS planValue, [PriorYear] AS priorYearValue
FROM (SELECT fiscalYear, period, type, metricName, metricValue
      FROM vw_ExecSummary
      UNPIVOT (metricValue FOR metricName IN ([Net Revenue], [Total C.S. Salaries])) unpvt) unpvt
PIVOT (SUM(metricValue) FOR type IN ([Actual], [Plan], [PriorYear])) AS pvt

    SQL Fiddle Example

    I have just one concern, though: values like 3676798.98999997, 3503920.077405, etc, make me think those columns are floating point (ie, REAL or FLOAT),but the values are named for monetary uses. If this is the case.... you are aware floating-point values can't store things like .1 exactly, right (ie, you can't actually add a dime to a value)? And that, when values get large enough, you can't add 1 anymore either? Usually when dealing with monetary values you should be using something based on a fixed-point type, like DECIMAL or NUMERIC.