以一个值为轴心，但将一行上的数据按另一行分组?

本文介绍了以一个值为轴心，但将一行上的数据按另一行分组?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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 |