SQL Server – Splitting a Result Row into Multiple Rows

sql serverunpivot

I have the following query:

SELECT
       vs.MonthName AS Month, 
       vs.Region, 
       vs.Category, 
       CAST (MIN (a.TotalAmount) AS money) AS ActualAmount, 
       MIN (a.TotalCases) AS ActualCases, 
       CAST (MIN (ve.BudgetAmount) AS money) AS BudgetAmount,
      MIN (ve.BudgetCases) AS BudgetCases
FROM
    dbo.VarianceSetup AS vs
    LEFT OUTER JOIN dbo.Actuals 
       AS a ON vs.MonthNum=a.Month
                AND vs.RegionId=a.SalesRegionId
                AND vs.CategoryId=a.ProductCategoryId
                AND a.Year=2015
    LEFT OUTER JOIN dbo.VarianceReportEntries 
       AS ve ON a.Month=ve.Month
             AND a.SalesRegionId=ve.SalesRegionId
             AND a.ProductCategoryId=ve.ProductCategoryId
             AND ve.Year=2015
GROUP BY
        vs.MonthName, 
        vs.Region, 
        vs.Category;

which gives me the following output.

+---------+-------------+----------+--------------+-------------+-------------+--------------+
|  Month  |   Region    | Category | ActualAmount | ActualCases | BudgetCases | BudgetAmount |
+---------+-------------+----------+--------------+-------------+-------------+--------------+
| January | New England | Cola     | 439365.6000  | 31200       | 0           | 0.0000       |
| January | New England | Juice    | 110051.7600  | 8424        | 0           | 0.0000       |
| January | New England | Water    | 800.0000     | 40          | 0           | 0.0000       |
| January | NY / NJ     | Cola     | 552583.0800  | 40692       | 0           | 0.0000       |
| January | NY / NJ     | Juice    | 139824.3600  | 10764       | 0           | 0.0000       |
| January | NY / NJ     | Water    | NULL         | NULL        | NULL        | NULL         |
+---------+-------------+----------+--------------+-------------+-------------+--------------+

But for some code I am working on, I need each row to be split into four rows and add a string identifier to indicate the type of row, like this:

+---------+-------------+----------+--------------+-------------+
|  Month  |   Region    | Category |     Type     |    Value    |
+---------+-------------+----------+--------------+-------------+
| January | New England | Cola     | ActualAmount | 439365.6000 |
| January | New England | Cola     | ActualCases  | 31200       |
| January | New England | Cola     | BudgetAmount | 0.0000      |
| January | New England | Cola     | BudgetCases  | 0           |
| January | New England | Juice    | ActualAmount | 110051.7600 |
| January | New England | Juice    | ActualCases  | 8424        |
| January | New England | Juice    | BudgetAmount | 0.0000      |
| January | New England | Juice    | BudgetCases  | 0           |
+---------+-------------+----------+--------------+-------------+

How can I accomplish that by tweaking my query — OR by creating a query based on the View of this query?

Best Answer

As Mark mentioned, UNPIVOT would be a good choice here. I often find it's easier to throw the original query into a temp table then unpivot that. You could use that approach, CTEs or views depending on performance. All the datatypes need to line up for it to work, so I'm casting everything to MONEY in my example, something like this:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
       vs.MonthName AS Month, 
       vs.Region, 
       vs.Category, 
       CAST (MIN (a.TotalAmount) AS MONEY) AS ActualAmount, 
       CAST (MIN (a.TotalCases)  AS MONEY) AS ActualCases, 
       CAST (MIN (ve.BudgetAmount) AS MONEY) AS BudgetAmount,
       CAST (MIN (ve.BudgetCases)  AS MONEY) AS BudgetCases
INTO #tmp
FROM
    dbo.VarianceSetup AS vs
    LEFT OUTER JOIN dbo.Actuals 
       AS a ON vs.MonthNum=a.Month
                AND vs.RegionId=a.SalesRegionId
                AND vs.CategoryId=a.ProductCategoryId
                AND a.Year=2015
    LEFT OUTER JOIN dbo.VarianceReportEntries 
       AS ve ON a.Month=ve.Month
             AND a.SalesRegionId=ve.SalesRegionId
             AND a.ProductCategoryId=ve.ProductCategoryId
             AND ve.Year=2015
GROUP BY
        vs.MonthName, 
        vs.Region, 
        vs.Category;
GO


SELECT [Month], Region, Category, ActualAmount, ActualCases, BudgetAmount, BudgetCases
FROM #tmp
UNPIVOT ( [Type] For [Value] In ( [ActualAmount], [ActualCases], [BudgetCases], [BudgetAmount] ) ) upvt
Related Question