是否可以使用SQL Server使用同一个枢轴列来具有多个枢轴 [英] Is it possible to have multiple pivots using the same pivot column using SQL Server
问题描述
我正面临以下挑战.我需要在同一列上两次旋转表数据. 这是数据的屏幕截图.
I am facing the following challenge. I need to rotate table data twice over the same column. Here's a screenshot of the data.
我希望每个物料ID都有一行,其中包含每年的购买价值和销售价值. 我尝试通过选择两次年份"列来进行此操作,对它进行了一些格式化,以便每个销售年份都以"S"为前缀,每个购买年份都以"P"开头,并使用2个枢轴围绕2年列进行旋转.这是SQL查询(在SQL Server 2008中使用):
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):
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):
如您所见,每个项目ID仍然有多于一行.有没有一种方法可以将每个项目的行数减少到恰好一个?这样看起来有点像下面的Excel屏幕截图?
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?
推荐答案
我的建议是同时应用UNPIVOT
和PIVOT
函数以获取结果.
My suggestion would be to apply both the UNPIVOT
and the PIVOT
functions to get the result.
UNPIVOT
将把PurchasingValue
和SellingValue
列变成行.完成此操作后,您就可以将数据转换为结果了.
The UNPIVOT
will turn the PurchasingValue
and SellingValue
columns into rows. Once this is done, then you can pivot the data into your result.
代码为:
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;
请参见带演示的SQL Fiddle .结果是:
| 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 |
在SQL Server 2008+中,您可以将CROSS APPLY
与VALUES
以及PIVOT
函数一起使用:
In SQL Server 2008+ you can use CROSS APPLY
with VALUES
along with the PIVOT
function:
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
请参见带有演示的SQL小提琴
这篇关于是否可以使用SQL Server使用同一个枢轴列来具有多个枢轴的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!