SQL Server 透视运算符简化

引言

在本技巧中，我将以一种非常简化的方式解释 Pivot 运算符。

根据定义，Pivot 是一个 SQL Server 运算符，可以将结果集中的一列的唯一值转换为输出中的多列，因此它看起来像是旋转了表格。

那么让我解释一下我所说的旋转表格是什么意思。

背景

我从 https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx 上了解到它。我只是想用一个简单的例子来解释它。

Using the Code

-- Syntax for Pivot operator
SELECT < non - pivoted COLUMN >
    ,[first pivoted column] AS < COLUMN NAME >
    ,[second pivoted column] AS < COLUMN NAME >
    ,...[last pivoted column] AS < COLUMN NAME >
FROM (
    < SELECT query that produces the data >
    ) AS < alias
FOR the source query >
PIVOT(< aggregation FUNCTION > (< COLUMN being aggregated >) _
FOR [<column that contains the values that will become column headers>] IN (
            [first pivoted column]
            ,[second pivoted column]
            ,...[last pivoted column]
            )) AS < alias
FOR the
pivot TABLE > < optional
ORDER BY clause >;

-- This is the code snippet

Create Table Customer
(
 CustomerName nvarchar(50),
 CustomerCountry nvarchar(50),
 SalesAmount int
)

Insert into Customer values('Tommy', 'UK', 200)
Insert into Customer values('Johny', 'US', 180)
Insert into Customer values('Johny', 'UK', 260)
Insert into Customer values('Dave', 'India', 450)
Insert into Customer values('Tommy', 'India', 350)
Insert into Customer values('Dave', 'US', 200)
Insert into Customer values('Tommy', 'US', 130)
Insert into Customer values('Johny', 'India', 540)
Insert into Customer values('Johny', 'UK', 120)
Insert into Customer values('Dave', 'UK', 220)
Insert into Customer values('Johny', 'UK', 420)
Insert into Customer values('Dave', 'US', 320)
Insert into Customer values('Tommy', 'US', 340)
Insert into Customer values('Tommy', 'UK', 660)
Insert into Customer values('Johny', 'India', 430)
Insert into Customer values('Dave', 'India', 230)
Insert into Customer values('Dave', 'India', 280)
Insert into Customer values('Tommy', 'UK', 480)
Insert into Customer values('Johny', 'US', 360)
Insert into Customer values('Dave', 'UK', 140)

-- lets see the result set in our table
Select * from Customer

现在您可以在上面的结果集中看到，客户名称有三个不同的值（Tommy、Johny、Dave），国家/地区也有三个（US、UK、India）。

---Now a simple Group By statement can produce a result-set like this :
Select CustomerCountry, CustomerName, SUM(SalesAmount) as Total
from Customer
GROUP BY CustomerCountry,CustomerName
ORDER BY CustomerCountry,CustomerName

您可以清楚地看到 CustomerCountry 有三个值（India、UK 和 US）。

Pivot 运算符的强大之处在于它可以实际旋转表格。让我们先看一下输出，然后我再用它的语法来解释它。

Pivot 运算符对 SalesAmount 列执行 SUM 聚合函数，针对每个不同的 CustomerCountry 列值。
您可以在输出数据集中看到 - India、UK、US 不同的值被透视到 CustomerCountry 列。

让我们看看它的简单语法

Select * from Customer
PIVOT
(
 SUM(SalesAmount) FOR CustomerCountry IN ([India],[UK],[US])
) AS Pivotable

------------------------------------------------------------------------------------------------------------------
现在您已经了解了 PIVOT，您也可以看到在 Customer 表中，有三个不同的客户名称（Dave、Johny 和 Tommy）。
因此，您可以通过对 CustomerName 列进行 Pivot Sum 聚合函数，将 [Dave]、[Johny] 和 [Tommy] 作为列标题。

Select * from Customer
PIVOT
(
 SUM(SalesAmount) FOR CustomerName IN ([Dave],[Johny],[Tommy])
) 
AS Pivotable

请参阅输出结果

现在就这些了。您已经掌握了 PIVOT 运算符的基本概念。
我发现另一个耗时的事情是在 [ ] 方括号中键入每个不同的列值，例如

[Dave],[Johny],[Tommy]

因此，您可以使用 SQL Server 中的 QuoteName、Stuff 函数和 XML 来简化此操作。

DECLARE @quotedcountrynames NVARCHAR(MAX)

SET @quotedcountrynames = STUFF((
            SELECT DISTINCT ',' + QUOTENAME(CustomerCountry)
            FROM Customer
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

PRINT @quotedcountrynames

您将获得如下输出

[Dave],[Johny],[Tommy]

或

[India],[UK],[US] 

您可以将此输出馈送到 IN 运算符中，以便为它们的列名分别透视这些值。

谢谢！就这些了 - 我希望您觉得它有用。

关注点

我找到了 Stuff 函数。对于 XML，它对于提取不同的列标题非常有用。如有任何疑问，请随时与我联系。