1

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?

2

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
-3

If I were doing this I would code it in Python (or another suitable language) and for each row I read in from the original file write four to the new file. In SQL it would be a case of combining four SELECT statements and then UNIONing them together.

  • Completely inappropriate choice of tool. – wBob Feb 6 '15 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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