以一个值为轴心，但将一行上的数据按另一行分组? [英] Pivot a table on a value but group the data on one line by another?

问题描述

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