多列上的 PIVOT

2022-01-16 00:00:00 pivot sql tsql sql-server sql-server-2008-r2

我有这样的数据:

Product Group     Product Level    Quatity Sold     Trend
==============================================================
Group 1           L1                10               up
Group 1           L2                20               up
Group 1           L3                30               down
Group 2           L1                20               up
Group 2           L2                40               up
Group 2           L3                60               down
Group 2           L4                80               down

我需要获取这种格式的数据:

I need to get the data in this format:

Product Group     L1      L1Trend    L2    L2Trend    L3    L3Trend    L4     L4Trend
======================================================================================
Group 1           10      up         20    up         30    down
Group 2           20      up         40    up         60    down       80     down

我能够通过使用类似的东西来关注产品级别":

I was able to pivot on "Product Level" by using something like:

PIVOT (MAX(quatity)     FOR productlevel IN([L1],[L2],[L3],[L4]) AS p

但在应对趋势时迷路了.

but got lost when dealing with the trend.

谢谢.

推荐答案

你可以通过实现 PIVOT 函数得到想要的结果，但我会先 UNPIVOT 你的 Quantity Sold 和 的多个列>趋势.unpivot 过程会将它们从多列转换为多行数据.

You could get the desired result by implementing the PIVOT function, but I would first UNPIVOT your multiple columns of Quantity Sold and Trend. The unpivot process will convert them from multiple columns into multiple rows of data.

由于您使用的是 SQL Server 2008+，您可以使用 CROSS APPLY 和 VALUES 来取消透视数据:

Since you are using SQL Server 2008+, you can use CROSS APPLY with VALUES to unpivot the data:

select [Product Group], 
  col, value
from yourtable
cross apply
(
  values
    ([Product Level], cast([Quatity Sold] as varchar(10))),
    ([Product Level]+'trend', [trend])
) c (col, value);

参见 SQL Fiddle with Demo 这会将您的表数据转换为以下格式:p>

See SQL Fiddle with Demo This converts your table data into the format:

| PRODUCT GROUP |     COL | VALUE |
|---------------|---------|-------|
|       Group 1 |      L1 |    10 |
|       Group 1 | L1trend |    up |
|       Group 1 |      L2 |    20 |
|       Group 1 | L2trend |    up |
|       Group 1 |      L3 |    30 |
|       Group 1 | L3trend |  down |

现在您可以轻松应用 PIVOT 功能:

Now you can easily apply the PIVOT function:

select [Product Group],
  L1, L1trend, 
  L2, L2trend,
  L3, L3trend, 
  L4, L4trend
from
(
  select [Product Group], 
    col, value
  from yourtable
  cross apply
  (
    values
      ([Product Level], cast([Quatity Sold] as varchar(10))),
      ([Product Level]+'trend', [trend])
  ) c (col, value)
) d
pivot
(
  max(value)
  for col in (L1, L1trend, L2, L2trend,
              L3, L3trend, L4, L4trend)
) piv;

参见 SQL Fiddle with Demo.这会给你一个最终结果:

See SQL Fiddle with Demo. This gives you a final result of:

| PRODUCT GROUP | L1 | L1TREND | L2 | L2TREND | L3 | L3TREND |     L4 | L4TREND |
|---------------|----|---------|----|---------|----|---------|--------|---------|
|       Group 1 | 10 |      up | 20 |      up | 30 |    down | (null) |  (null) |
|       Group 2 | 20 |      up | 40 |      up | 60 |    down |     80 |    down |

相关文章