请解释 PIVOT 的各个部分

问题描述

我已经阅读了很多博客文章.我已阅读文档.我通常很擅长学习新东西，但即使我一直在阅读，但我只是不了解 SQL Server (2008) 中 PIVOT 的部分内容.

有人可以把它给我吗，又好又慢.(即 Pivot for Dummies)

如果需要示例，我们可以使用 in this question.p>

以下是我尝试转换该示例的方式:

选择其他ID、Val1、Val2、Val3、Val4、Val5从(选择其他 ID，Val来自@randomTable) p枢(最大值(val)用于 Val IN(Val1、Val2、Val3、Val4、Val5)) 作为数据透视表；

上面的查询给了我空值，而不是 Val1、Val2... 列中的值.

但要明确一点，我不是在这里寻找固定查询.我需要理解 PIVOT，因为我正在寻找比这个示例更复杂的东西.

具体是什么处理聚合?我只想获取与给定 ID 匹配的所有字符串值并将它们放在同一行中.我不想汇总任何东西.(同样，请参阅 this question 作为我的示例.)

透视查询说明

FROM(SELECT OtherID, Val, 金额来自@randomTable) p

这些是成为数据透视的基础数据"的列.不要包含不执行任何操作的列.正如您不会将非 GROUP BY 列放入 SELECT 子句一样，您也不会在 PIVOT 源中列出未使用的列.

PIVOT(最大(数量)用于 Val IN(Val1、Val2、Val3、Val4、Val5)) 作为数据透视表；

这部分说您正在创建 5 个名为Val1"到Val5"的新列.这些列名代表 Val 列中的值.所以预计您的表格将包含类似这样的内容

otherID Val 数量1 值 1 12 值 2 21 值 3 31 值 1 5(等)(此列包含 Val1 - Val5 之一，或 null)

因此，您现在有了 5 个以前不存在的新列.列中的内容是什么?

• 出现在 OUTPUT 中但不是 PIVOTed 列的任何列都是GROUP BY"列.
• 聚合函数将所有数据收集到 GROUP BY 列和 PIVOTED 列之间的交叉单元格中.

因此，为了说明，使用上面的示例数据，我们有 otherID=1 和 val=Val1.在输出表中，对于每个 (otherID/val) 组合，只有一个单元格表示 Max(amount) 的这种组合

otherID Val1 Val2 Val3 Val4 Val51 <x>………………(等等)

对于标记为 <x> 的单元格，只允许一个值，因此 <x> 不能包含多个 amount 值.这就是我们需要聚合它的原因，在本例中使用 MAX(amount).所以其实输出是这样的

(unpivoted columns) (pivoted, 创建新"列)其他ID |值 1 值 2 值 3 值 4 值 51 |MAX(数量) Max(数量) <<单元格值 = 聚合函数(等等)

SELECT 语句随后输出这些列

选择其他ID、Val1、Val2、Val3、Val4、Val5

I have read lots of blog posts. I have read the docs. I am usually fairly good at picking up new stuff but even though I keep reading, but I just don't understand the parts of a PIVOT in SQL Server (2008).

Can someone please give it to me, nice and slow. (ie Pivot for Dummies)

If an example is needed then we can use the one in this question.

Here is how I tried to pivot that example:

SELECT OtherID, Val1, Val2, Val3, Val4, Val5
FROM 
    (SELECT OtherID, Val
    FROM @randomTable) p
PIVOT
(
    max(val)
    FOR Val IN (Val1, Val2, Val3, Val4, Val5)
) AS PivotTable;

The above query gives me nulls instead of values in the Val1, Val2... columns.

But to be clear, I am not looking for a fixed query here. I need to understand PIVOT as I am looking to pivot something far more complex than this example.

Specifically what is the deal with the aggregate? I just want to take all string values that match on a given ID and put them in the same row. I am not trying to aggregate anything. (Again, see this question for my example.)

Explanation of the pivot query

FROM 
    (SELECT OtherID, Val, amount
    FROM @randomTable) p

These are the columns that become the "base data" for the pivot. Do not include columns that don't do anything. Just as you don't put non-GROUP BY columns into the SELECT clause, you don't list out unused columns in a PIVOT source.

PIVOT
(
    max(amount)
    FOR Val IN (Val1, Val2, Val3, Val4, Val5)
) AS PivotTable;

This part says that you are creating 5 new columns named "Val1" through "Val5". These column names represent values in the column Val. So it is expected that your table will contain something like this

otherID   Val     amount
1         Val1    1
2         Val2    2
1         Val3    3
1         Val1    5
(etc)     (this column contains one of Val1 - Val5, or null)

So you now have 5 new columns that did not exist before. What goes into the column?

• Any column that appears in the OUTPUT that is not a PIVOTed column is a "GROUP BY" column.
• The aggregate function is what collects all the data into the cell that is the CROSS between the GROUP BY columns and the PIVOTED column.

So, to illustrate, using the sample data above, we have otherID=1 and val=Val1. In the output table, there is only one cell representing this combination of Max(amount) for each (otherID/val) combination

otherID   Val1   Val2   Val3   Val4   Val5
1         <x>    ...    ...    ...    ...
(etc)

For the cell marked <x> , only one value is allowed, so <x> cannot contain multiple amount values. That is the reason why we need to aggregate it, in this case using MAX(amount). So in fact, the output looks like this

(unpivoted columns)   (pivoted, creates "new" columns)
otherID             |  Val1          Val2           Val3   Val4   Val5
1                   |  MAX(amount)   Max(amount)    << cell value = aggregate function
(etc)

The SELECT statement is what then outputs these columns

SELECT OtherID, Val1, Val2, Val3, Val4, Val5

这篇关于请解释 PIVOT 的各个部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，WP2