主页 / dba / 问题 / 91281
Accepted
NaNerd
Asked: 2015-02-07 02:02:26 +0800 CST

将结果行拆分为多行

  • 772

我有以下查询：

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;

这给了我以下输出。

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

但是对于我正在处理的一些代码，我需要将每一行分成四行并添加一个字符串标识符来指示行的类型，如下所示：

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

我怎样才能通过调整我的查询来实现这一点——或者通过基于这个查询的视图创建一个查询？

sql-server unpivot

2 个回答

  1. Best Answer

    正如 Mark 提到的，UNPIVOT将是一个不错的选择。我经常发现将原始查询放入临时表然后取消透视表更容易。您可以根据性能使用该方法、CTE 或视图。所有数据类型都需要排队才能工作，所以我MONEY在我的例子中将所有内容都转换为，如下所示：

    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
    • 2

  2. 如果我这样做，我会用 Python（或另一种合适的语言）对其进行编码，并且对于我从原始文件中读取的每一行，将四个写入新文件。在 SQL 中，这将是组合四个 SELECT 语句然后将它们联合在一起的情况。

    • -3

相关问题