问题描述
我有两张数据表
表 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 |
我需要一个会产生的查询
I need a query which will produce
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.
推荐答案
这应该有效:
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 表上放置条件时，巨大的差异就会发挥作用 - 您应该测试并查看哪个查询效果更好.
These have the same result set 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.
注意:在使用 PIVOT 时，只有应该作为结果输出一部分的列可用，这一点至关重要.这就是为什么上述两个查询具有额外的派生表子查询 (SELECT ...) 以便仅公开特定列的原因.PIVOT 可以看到的所有列都没有在数据透视表达式中列出，它们将被隐式分组并包含在最终输出中.这可能不是您想要的.
Note: it is crucial when using PIVOT that only the columns that should be part of the resulting output are available. This is why the two above queries have extra derived table subqueries (SELECT ...) so that only specific columns are exposed. All columns that are available to be seen by PIVOT that aren't listed in the pivot expression will implicitly be grouped on and included in the final output. This will likely not be what you want.
但是，我可以建议您在表示层中进行旋转吗?例如，如果您正在使用 SSRS，那么使用矩阵控件就很容易为您完成所有旋转.这是最好的，因为如果你添加一个新的Category，你就不会修改你所有的 SQL 代码！
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!
有一种方法可以动态查找要透视的列名，但它涉及动态 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.
另一种可以工作的方法是预处理这个查询——意思是在Category表上设置一个触发器，重写一个视图以包含所有现存的类别.这确实解决了我提到的许多其他问题，但同样，最好使用表示层.
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.
注意:如果您的列名(以前是值)有空格、是数字或以数字开头，或者不是有效的标识符，您必须用方括号将它们括起来，如 PIVOT (Max(Value) FOR CategoryId IN ([1], [2], [3], [4])) P.或者，您可以在它们到达查询的 PIVOT 部分之前修改这些值以添加一些字母或删除空格，这样列列表就不需要转义了.如需进一步阅读，请查看 SQL Server 中标识符的规则.
Note: If your column names (that were formerly values) have spaces, are numbers or begin with a number, or are otherwise not valid identifiers, 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 or remove spaces, so that the column list doesn't need escaping. For further reading on this check out the rules for identifiers in SQL Server.
这篇关于SQL Server 连接表和数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！