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

Answer 1

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