SQL Server连接表和数据透视表

Question

我有两个带数据的表格

表格 1

    ---------------------------------------------------
    | SALEID | SOLDBY | SALEPRICE | MARGIN |   DATE   |
    |  1     |  'aa'  |  10,000   |   10   | 2013-1-1 |
    |  2     |  'bb'  |  25,000   |    5   | 2013-5-1 |

表格2

    ---------------------------------------------------
    | SALEITEMID | SALEID | SALEPRICE | CATEGORY |
    |  1         |  1     |   6,000   | BOOKS    |
    |  2         |  1     |   4,000   | PRINTING |
    |  3         |  2     |   5,000   | BOOKS    |
    |  4         |  2     |   12,000  | PRINTING |
    |  5         |  2     |   8,000   | DVD      |

我需要一个能够产生TAB3的查询。

    --------------------------------------------------------------------------------
    | SALEID | SOLDBY | SALEPRICE | MARGIN |   DATE   |  BOOKS  | PRINTING | DVD
    |  1     |  'aa'  |  10,000   |   10   | 2013-1-1 |  6,000  |  4,000   | 0
    |  2     |  'bb'  |  25,000   |    5   | 2013-5-1 |  5,000  | 12,000   | 8,000

我对数据透视表操作很陌生，不确定透视表是否适合这种情况。

- Null Head

---

有多少个类别？这是完整的列表吗？将来可能会有更多吗？ - ErikE

大约有7个类别。不会超过这个数量。 - Null Head

Answer 1

这应该可以工作：

WITH Sales AS (
   SELECT
      S.SaleID,
      S.SoldBy,
      S.SalePrice,
      S.Margin,
      S.Date,
      I.SalePrice,
      I.Category
   FROM
      dbo.Sale S
      INNER JOIN dbo.SaleItem I
         ON S.SaleID = I.SaleID
)
SELECT *
FROM
   Sales
   PIVOT (Max(SalePrice) FOR Category IN (Books, Printing, DVD)) P
;

或者说：

SELECT
   S.SaleID,
   S.SoldBy,
   S.SalePrice,
   S.Margin,
   S.Date,
   I.Books,
   I.Printing,
   I.DVD
FROM
   dbo.Sale S
   INNER JOIN (
      SELECT *
      FROM
         (SELECT SaleID, SalePrice, Category FROM dbo.SaleItem) I
         PIVOT (Max(SalePrice) FOR Category IN (Books, Printing, DVD)) P
   ) I ON S.SaleID = I.SaleID
;

这些查询产生相同的结果集，并且实际上可能被查询优化器视为相同，但也可能不是。当您开始在 Sale 表上放置条件时，差异就出现了——您应该测试并查看哪个查询效果更好。

注意：使用 PIVOT 时至关重要的一点是，只有应包含在生成输出中的列才可用。这就是为什么上面的两个查询具有额外的派生表子查询 (SELECT ...)，以便仅公开特定的列。所有可供 PIVOT 看到的列（未列在枢轴表达式中）将被隐式分组并包含在最终输出中。这通常不是您想要的。

不过，我建议您在演示层中进行透视。例如，如果您正在使用 SSRS，则可以很容易地使用矩阵控件来完成所有透视工作。那是最好的，因为如果您添加了新的 Category，您就不必修改所有 SQL 代码！

有一种方法可以动态找到要透视的列名，但它涉及动态 SQL。尽管这是可能的，但我不认为这是最佳方式。

另一种可能有效的方法是预处理此查询——也就是在 Category 表上设置触发器，以重写视图以包含所有现有类别。这确实解决了我提到的许多其他问题，但是再次强调，使用演示层是最好的。

请注意：如果您的列名（曾经是值）包含空格、数字或以数字开头，或者以其他方式不是有效标识符，则必须用方括号引用它们，如 PIVOT (Max(Value) FOR CategoryId IN ([1], [2], [3], [4])) P。或者，您可以在进入查询的 PIVOT 部分之前修改这些值，以添加一些字母或删除空格，以便列列表不需要转义。要了解更多信息，请查看 SQL Server 中用于标识符的规则。

- ErikE

---

非常感谢，真的很有帮助......第一个查询已经起作用，在第一个查询中请在 INNER JOIN dbo.SaleItem 后面添加 'I' 或者 INNER JOIN dbo.SaleItem I。谢谢。 - panky sharma

请确保检查Max()是否适用 - 如果每个SaleID + Category可能有多个，则可能需要使用Sum()或某种Row_Number()预计算，以便值可以在单独的行上。 - ErikE