多列PIVOT [英] PIVOT on Multiple Columns

问题描述

我有这样的数据:

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函数来获得所需的结果，但是我首先要对Quantity Sold和Trend的多列进行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小提琴它将表数据转换为以下格式:

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提琴.得到的最终结果是:

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 |

这篇关于多列PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！