在值上旋转表格，但将一行上的数据分组？

一尘不染

在值上旋转表格，但将一行上的数据分组？

sql

我的桌子

CREATE TABLE #table
    ([Indicator] int, [Scenario_code] smallint, [period] nvarchar(50), [Value] int, [AREA code] nvarchar(10), [Release_Code] int)
;

INSERT INTO #table
    ([Indicator], [Scenario_code], [period], [Value], [AREA code], [Release_Code])
VALUES
    (2, 7, '2000-06-13', 1000, 'OP014', 17),
    (2, 16, '2000-09-12', 1100, 'OP014', 17),
    (2, 17, '2002-06-22', 1200, 'OP014', 17),
    (3, 7, '2000-01-12', 1300, 'OP014', 17),
    (3, 16, '2000-06-17', 500, 'OP014', 17),
    (3, 17, '2008-05-04', 550, 'OP014', 17),
    (4, 7, '2000-06-12', 600, 'OP014', 17),
    (4, 16, '2000-12-12', 650, 'OP014', 17),
    (4, 17, '2013-06-12', 150, 'OP014', 17)

我希望基于[indicator]和[scenario_code]字段对[period]和[Value]字段进行某种方式的透视。有三个指标值（2,3,4）和三个方案代码（7,16,17）。我正在寻找按scenario_code对行进行分组，并将每个对应的指标值作为其自己的字段。结果为三行，应如下所示。

{[Scernario_code]，[Period 2]，[Value 2]，[Period 3]，[Value 3]，[Period
4]，[Value 4]，[Area Code]，[Release code]}

7，‘2000-06-13’，1000，‘2000-01-12’，1300，‘2000-06-12’，600，’OP014’，17

16，‘2000-09-12’，1100，‘2000-06-17’，500，‘2000-12-12’，650，’OP014’，17

17，‘2002-06-22’，1200，‘2008-05-04’，550，‘2013-06-12’，150，’OP014’，17

基于绑定到一个[scenario_code]的三个指标值（2,3,4），已将period和value列分散。我已经在列的后缀上加上了它被枢转的指标值。理想情况下，我会将它们作为其他别名。

念头

这显然是在尖叫枢轴或枢轴（甚至是两者），但是我的教科书没有什么地方需要考虑两列作为传播元素[period]和[Value]。我需要按指标值旋转数据，因此它们是列，但与场景代码分组在同一行。也许级联会有所帮助…？

我已经看到了CROSS APPLY与Pivot的结合，它看起来很有希望，但由于无法完全理解它的使用方式，因此我无法使其正常工作。我最近开始使用SQL
Server 2012。

阅读 188

2021-03-17

共1个答案

一尘不染

获得结果的最简单方法是使用带有CASE表达式的聚合函数：

select
  scenario_code,
  max(case when indicator = 2 then period end) [Period 2],
  max(case when indicator = 2 then value end) [Value 2],
  max(case when indicator = 3 then period end) [Period 3],
  max(case when indicator = 3 then value end) [Value 3],
  max(case when indicator = 4 then period end) [Period 4],
  max(case when indicator = 4 then value end) [Value 4],
  [area code],
  Release_Code
from yourtable
group by scenario_code, [area code], Release_Code

参见带有演示的SQL Fiddle

但是您可以使用PIVOT函数获取结果，但由于要在两列上进行透视，因此还需要先取消对PeriodandValue列的透视。

由于您使用的是SQL Server 2012，因此可以将CROSS APPLY与VALUES一起使用来取消透视。基本语法为：

select scenario_code, [area code], release_code,
  col = col +' ' +cast(indicator as varchar(10)),
  val
from yourtable
cross apply
(
  values
    ('Period', convert(varchar(10), period, 120)),
    ('Value', convert(varchar(10), value))
) c (col, val);

请参阅带有演示的SQL Fiddle。这将使您的数据成为以下格式：

| SCENARIO_CODE | AREA CODE | RELEASE_CODE |      COL |        VAL |
|---------------|-----------|--------------|----------|------------|
|             7 |     OP014 |           17 | Period 2 | 2000-06-13 |
|             7 |     OP014 |           17 |  Value 2 |       1000 |
|            16 |     OP014 |           17 | Period 2 | 2000-09-12 |
|            16 |     OP014 |           17 |  Value 2 |       1100 |

您会注意到，我们必须将这两个列都强制转换/转换为相同的数据类型，才能正常运行该过程。取消透视数据后，即可轻松应用PIVOT函数并将值转换COL为新的列标题：

select scenario_code,
  [Period 2], [Value 2],
  [Period 3], [Value 3],
  [Period 4], [Value 4],
  [area code], release_code
from
(
  select scenario_code, [area code], release_code,
    col = col +' ' +cast(indicator as varchar(10)),
    val
  from yourtable
  cross apply
  (
    values
      ('Period', convert(varchar(10), period, 120)),
      ('Value', convert(varchar(10), value))
  ) c (col, val)
) d
pivot
(
  max(val)
  for col in ([Period 2], [Value 2],
              [Period 3], [Value 3],
              [Period 4], [Value 4])
) piv;

请参阅带有演示的SQL Fiddle。两种版本的最终结果均为：

| SCENARIO_CODE |   PERIOD 2 | VALUE 2 |   PERIOD 3 | VALUE 3 |   PERIOD 4 | VALUE 4 | AREA CODE | RELEASE_CODE |
|---------------|------------|---------|------------|---------|------------|---------|-----------|--------------|
|             7 | 2000-06-13 |    1000 | 2000-01-12 |    1300 | 2000-06-12 |     600 |     OP014 |           17 |
|            16 | 2000-09-12 |    1100 | 2000-06-17 |     500 | 2000-12-12 |     650 |     OP014 |           17 |
|            17 | 2002-06-22 |    1200 | 2008-05-04 |     550 | 2013-06-12 |     150 |     OP014 |           17 |

2021-03-17