是否可以使用同一个pivot列使用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.

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

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设置一行，其中包含每年的采购价值和销售价值。我尝试通过两次选择“year”列来实现这一点，并对其进行一些格式化，这样每年的销售年份都以“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截图?

3 个解决方案

#1

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

我的建议是同时应用主元和主元函数来得到结果。

The UNPIVOT will turn the PurchasingValue and SellingValue columns into rows. Once this is done, then you can pivot the data into your result.

UNPIVOT将把购买值和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小提琴演示。其结果是:

| 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 APPLY with VALUES along with the PIVOT function:

在SQL Server 2008+中，您可以使用带有值的CROSS APPLY和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

See SQL Fiddle with Demo

参见SQL小提琴演示

#2

Use a GROUP BY ItemID, with aggregate function SUM(isnull(value,0)) on each of the results columns.

使用GROUP BY ItemID，在每个结果列上使用聚合函数SUM(isnull(值，0))。

#3

One easy way to pivot multiple columns is to just use Aggregate(Case) expressions.

使用聚合(大小写)表达式是一种简单的对多个列进行透视的方法。

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]