请解释轴心的各部分

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).

我读过很多博客文章。我读过文档。我通常很擅长学习新东西，尽管我一直在阅读，但我就是不理解SQL Server(2008)中的PIVOT部分。

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.

上面的查询给了我nulls，而不是Val1、Val2中的值……列。

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.)

具体地说，与总资产的交易是什么?我只想取在给定ID上匹配的所有字符串值，并将它们放在同一行。我并没有试图聚合任何东西。(同样，以我的例子来看这个问题。)

1 个解决方案

#1

Explanation of the pivot query

对pivot查询的解释

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.

这些列将成为轴心的“基本数据”。不要包含不做任何事情的列。正如在SELECT子句中不按列进行非分组一样，在PIVOT源中也不列出未使用的列。

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

这部分说明您正在通过“Val5”创建5个名为“Val1”的新列。这些列名表示列Val中的值，因此您的表将包含如下内容。

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?

现在你有5个以前不存在的新列。列里有什么?

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=1 val=Val1。在输出表中，只有一个单元格表示每个(otherID/val)组合的最大值(amount)的组合。

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

对于标记为的单元格，只允许一个值，因此不能包含多个值。这就是为什么我们需要聚合它，在这种情况下使用MAX(amount)。实际上，输出是这样的

(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语句将输出这些列。

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

#1