是否可以使用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屋！