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?
Aucun commentaire:
Enregistrer un commentaire