是否可以使用 SQL Server 使用相同的数据透视列有多个数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15274305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to have multiple pivots using the same pivot column using SQL Server
提问by Rob Vermeulen
I am facing the following challenge. I need to rotate table data twice over the same column. Here's a screenshot of the data.
我正面临以下挑战。我需要在同一列上旋转表数据两次。这是数据的屏幕截图。
I want to have one row for each Item ID containing both the purchasing value and the selling value for each year. I tried doing this by selecting the "year" column twice, formatting it a bit so each selling year gets prefixed with a "S" and each purchasing year begins with a "P", and using 2 pivots to rotate around the 2 year columns. Here's the SQL query (used in SQL Server 2008):
我希望每个项目 ID 都有一行，其中包含每年的购买价值和销售价值。我尝试通过两次选择“年份”列来执行此操作，对其进行格式化，以便每个销售年份都以“S”为前缀，每个购买年份以“P”开头，并使用 2 个枢轴围绕 2 年列旋转. 这是 SQL 查询（在 SQL Server 2008 中使用）：
SELECT [Item ID],
[P2000],[P2001],[P2002],[P2003],
[S2000],[S2001],[S2002],[S2003]
FROM
(
SELECT [Item ID]
,'P' + [Year] AS YearOfPurchase
,'S' + [Year] AS YearOfSelling
,[Purchasing value]
,[Selling value]
FROM [ItemPrices]
) AS ALIAS
PIVOT
(
MIN ([Purchasing value]) FOR [YearOfPurchase] in ([P2000],[P2001],[P2002],[P2003])
)
AS pvt
PIVOT
(
MIN ([Selling value]) FOR [YearOfSelling] in ([S2000],[S2001],[S2002],[S2003])
)
AS pvt2
The result is not exactly what I was hoping for (see image below):
结果并不完全是我所希望的（见下图）：
As you can see, there are still more than one row for each item ID. Is there a way to reduce the number of rows to exactly one per item? So that it looks a bit like the Excel screenshot below?
如您所见，每个项目 ID 仍然有不止一行。有没有办法将每个项目的行数减少到一个？所以它看起来有点像下面的Excel截图？
回答by Taryn
My suggestion would be to apply both the UNPIVOTand the PIVOTfunctions to get the result.
我的建议是同时应用UNPIVOT和PIVOT函数来获得结果。
The UNPIVOTwill turn the PurchasingValueand SellingValuecolumns into rows. Once this is done, then you can pivot the data into your result.
该UNPIVOT会变成PurchasingValue和SellingValue列转换为行。完成此操作后，您可以将数据转换为结果。
The code will be:
代码将是：
select *
from
(
select itemid,
case
when col = 'PurchasingValue' then 'P'
when col = 'SellingValue' then 'S'
end + cast(year as varchar(4)) new_col,
value
from yourtable
unpivot
(
value
for col in ([PurchasingValue], [SellingValue])
) unpiv
) src
pivot
(
max(value)
for new_col in (P2000, P2001, P2002, P2003,
S2000, S2001, S2002, S2003)
) piv;
See SQL Fiddle with Demo. The result is:
请参阅SQL Fiddle with Demo。结果是：
| ITEMID | P2000 | P2001 | P2002 | P2003 | S2000 | S2001 | S2002 | S2003 |
--------------------------------------------------------------------------
| 1 | 1000 | 1100 | 1200 | 1300 | 900 | 990 | 1080 | 1170 |
| 2 | 500 | 550 | 600 | 650 | 450 | 495 | 540 | 585 |
In SQL Server 2008+ you can use CROSS APPLYwith VALUESalong with the PIVOTfunction:
在SQL Server 2008+可以使用CROSS APPLY与VALUES与沿PIVOT功能：
select *
from
(
select itemid,
col+cast(year as varchar(4)) new_col,
value
from yourtable
cross apply
(
VALUES
(PurchasingValue, 'P'),
(SellingValue, 'S')
) x (value, col)
) src
pivot
(
max(value)
for new_col in (P2000, P2001, P2002, P2003,
S2000, S2001, S2002, S2003)
) piv
回答by JamieD77
One easy way to pivot multiple columns is to just use Aggregate(Case) expressions.
旋转多个列的一种简单方法是仅使用 Aggregate(Case) 表达式。
SELECT [Item ID],
[P2000] = SUM(CASE WHEN [Year] = 2000 THEN [Purchasing value] END),
[P2001] = SUM(CASE WHEN [Year] = 2001 THEN [Purchasing value] END),
[P2002] = SUM(CASE WHEN [Year] = 2002 THEN [Purchasing value] END),
[P2003] = SUM(CASE WHEN [Year] = 2003 THEN [Purchasing value] END),
[S2000] = SUM(CASE WHEN [Year] = 2000 THEN [Selling value] END),
[S2001] = SUM(CASE WHEN [Year] = 2001 THEN [Selling value] END),
[S2002] = SUM(CASE WHEN [Year] = 2002 THEN [Selling value] END),
[S2003] = SUM(CASE WHEN [Year] = 2003 THEN [Selling value] END)
FROM ItemPrices
GROUP BY [Item ID]
回答by Pieter Geerkens
Use a GROUP BY ItemID, with aggregate function SUM(isnull(value,0)) on each of the results columns.
使用 GROUP BY ItemID，并在每个结果列上使用聚合函数 SUM(isnull(value,0))。
