Pivot a table on a value but group the data on one line by another?

Question

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.

Not always, my [period] and [value] fields can have nulls and I'm happy to have nulls in my columns. But if you're regarding col count, there will always be three [period] fields and three [value] fields to a corresponding scenario_code. Shame they're values, I've seen some groovy self join answers — Useless_Wizard, Dec 17 '13 at 11:32

KumarHarsh · Answer 1 · 2013-12-17T13:02:06.150

try this,

;with CTE as
(select *,ROW_NUMBER()over(partition by Scenario_code order by period)rn from #table)

select distinct a.Scenario_code, b.period [period2],b.Value [Value2],c.period [period3],c.Value [Value3],d.period [period4],d.Value [Value4]
from CTE a left join CTE b on a.Scenario_code=b.Scenario_code and b.rn=1
left join CTE c on a.Scenario_code=c.Scenario_code and c.rn=2
left join CTE d on a.Scenario_code=d.Scenario_code and d.rn=3
drop table #table

Check Latest,then i didn't notice indicator

Select * from 
(select ROW_NUMBER()over(partition by a.Scenario_code order by a.Scenario_code)rn ,  a.Scenario_code, b.period [period2],b.Value [Value2],c.period [period3],c.Value [Value3],d.period [period4],d.Value [Value4]
from #table a left join #table b on a.Scenario_code=b.Scenario_code and b.indicator=3
left join #table c on a.Scenario_code=c.Scenario_code and c.indicator=4
left join #table d on a.Scenario_code=d.Scenario_code and d.indicator=2
)t4 where rn=1

Works brilliantly, can I ask how you saw the logic in this? I wouldn't have considered this as an option. Is there a work around not to use DISTINCT? — Useless_Wizard, Dec 17 '13 at 12:43
Just compared the table and [Values] and [Period] fields are sorted and distinct is removed, nicely done, this is the answer I needed. Thank you @kumarHarsh — Useless_Wizard, Dec 17 '13 at 13:44

score 1 · Accepted Answer · answered Dec 17 '13 at 13:47

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 |

Pivot a table on a value but group the data on one line by another?

Thoughts

2 Answers2

Linked