在值上旋转表格，但将一行上的数据分组? [英] Pivot a table on a value but group the data on one line by another?

查看：62
本文介绍了在值上旋转表格，但将一行上的数据分组?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的桌子

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]& [价值].我需要按指标值旋转数据，因此它们是列，但与场景代码分组在同一行.也许级联会有所帮助...?

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

解决方案

获得结果的最简单方法是使用带有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提琴

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

由于您使用的是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提琴.这将使您的数据成为以下格式:

| 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提琴.两种版本的最终结果均为:

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

My table 

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)

I'd like the fields [period] and [Value] to be pivoted somehow based on their [indicator] and [scenario_code] fields. There are three indicator values (2,3,4) and three scenario codes (7,16,17). I'm looking to group the rows by scenario_code and have each corresponding indicator value as it's own field. The result, three rows, should look like this.

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

The period and value columns have been spread across, based on their three indicator values(2,3,4) which are bound to one [scenario_code]. I've suffixed the columns with the indicator value it was pivoted on. Ideally I will alias them as something else.

Thoughts

This is obviously screaming pivot or unpivot (Or even both) but my text books don't have something where I need to consider two columns for the spreading element [period] & [Value]. I need data to be rotated by indicator value so they are columns, but grouped on the same line as it's scenario code. Maybe a concatenation would help...?

I've seen CROSS APPLY with a Pivot which looks promising but I haven't been able to get it to work as I don't fully understand how this is utilised. I've recently started using SQL Server 2012.

解决方案

The simplest way to get the result would be using an aggregate function with a CASE expression:

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

See SQL Fiddle with Demo

But you can use the PIVOT function to get the result but you would also need to unpivot the Period and Value columns first, since you want to pivot on two columns.

Since you are using SQL Server 2012 you can use CROSS APPLY with VALUES to unpivot. The basic syntax will be:

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

See SQL Fiddle with Demo. This is going to get your data into the format:

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

You'll notice that we had to cast/convert both columns to the same datatype in order for this unpivoting process to work. Once the data has been unpivoted, then you can easily apply the PIVOT function and convert your values in COL to the new column headers:

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;

See SQL Fiddle with Demo. Both versions give a final result of:

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

这篇关于在值上旋转表格，但将一行上的数据分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆