是否可以使用 SQL Server 使用相同的数据透视列进行多个数据透视

2022-01-22 00:00:00 pivot sql sql-server unpivot

我正面临以下挑战.我需要在同一列上旋转表数据两次.这是数据的截图.

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?

推荐答案

我的建议是同时应用 UNPIVOTPIVOT 函数来获得结果.

My suggestion would be to apply both the UNPIVOT and the PIVOT functions to get the result.

UNPIVOT 会将 PurchasingValueSellingValue 列转换为行.完成此操作后,您可以将数据转换为结果.

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

在 SQL Server 2008+ 中,您可以使用 CROSS APPLYVALUES 以及 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 Fiddle with Demo

相关文章