SQL Server连接表和数据透视表

I have two tables with data

我有两个数据表

TABLE 1

表格1

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

TABLE 2

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

I need a query which will produce

我需要一个会产生的查询

TAB3

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

I am pretty new to pivoting and not sure if pivot is way to go for this or not.

我是一个很新的旋转，不知道枢轴是否适合这一点。

1 个解决方案

#1

This should work:

这应该工作：

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
;

Or alternately:

或者替代地：

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
;

These have the same resultset and may in fact be treated the same by the query optimizer, but possibly not. The big difference comes into play when you start putting conditions on the Sale table--you should test and see which query works better.

它们具有相同的结果集，实际上可能由查询优化器处理相同，但可能不是。当您开始在Sale表上添加条件时，最大的区别就在于它 - 您应该测试并查看哪个查询更好。

May I suggest, however, that you do the pivoting in the presentation layer? If, for example, you are using SSRS it is quite easy to use a matrix control that will do all the pivoting for you. That is best, because then if you add a new Category, you won't have modify all your SQL code!

但是，我可以建议您在表示层中进行旋转吗？例如，如果您正在使用SSRS，则可以非常轻松地使用矩阵控件来完成所有的旋转。这是最好的，因为如果你添加一个新的类别，你将不会修改所有的SQL代码！

There is a way to dynamically find the column names to pivot, but it involves dynamic SQL. I don't really recommend that as the best way, either, though it is possible.

有一种方法可以动态查找要转移的列名，但它涉及动态SQL。尽管有可能，我也不建议这是最好的方式。

Another way that could work would be to preprocess this query--meaning to set a trigger on the Category table that rewrites a VIEW to contain all the extant categories that exist. This does solve a lot of the other problems I've mentioned, but again, using the presentation layer is best.

另一种可行的方法是预处理此查询 - 意味着在Category表上设置一个触发器，该触发器重写VIEW以包含所有现存的类别。这确实解决了我提到的很多其他问题，但同样，使用表示层是最好的。

Note: If your column names (that were formerly values) are numbers or begin with a number, you must quote them with square brackets as in PIVOT (Max(Value) FOR CategoryId IN ([1], [2], [3], [4])) P. Alternately, you can modify the values before they get to the PIVOT part of the query to prepend some letters, so that the column list doesn't need escaping. For further reading on this check out the rules for identifiers in SQL Server.

注意：如果您的列名称（以前是值）是数字或以数字开头，则必须使用方括号引用它们，如PIVOT（Max（Value）FOR CategoryId IN（[1]，[2]，[3] ，[4]））P。或者，您可以在它们到达查询的PIVOT部分之前修改这些值以预先添加一些字母，以便列列表不需要转义。有关此内容的进一步阅读，请查看SQL Server中的标识符规则。

#1