Pivot and Unpivoting Data Using TSQL in SQL Server

Question

Report_Gap  Report_Group            Name            Percentage_Difference
1 Year      ABC                     Operating       3.9
1 Year      ABC                     Other           6.8
1 Year      DEF                     Other           5.7
1 Year      DEF                     Operating       1.2
1 Year      GHI                     Operating       6.5
1 Year      GHI                     Other           -2.0
5 Year      ABC                     Operating       13.6
5 Year      ABC                     Other           5.4
5 Year      DEF                     Other           5.5
5 Year      DEF                     Operating       15.4
5 Year      GHI                     Operating       19.5
5 Year      GHI                     Other           -7.9

Using Transact-SQL, Is it possible for data in the format above to be represent as below?

Report_Gap  Report_Group    Operating   Other
1 Year      ABC             3.9         6.8 
1 Year      DEF             1.2         5.7
1 Year      GHI             6.5         -2.0
5 Year      ABC             13.6        5.4
5 Year      DEF             15.4        5.5 
5 Year      GHI             19.5        -7.9

I have built the data to so that you don't have to.

with MyData
as
(
select '1 Year' as Report_Gap,
    'ABC' as Report_Group,
    'Operating' as Name,
    3.9 as Percentage_Difference
union all
select '1 Year' as Report_Gap,
    'ABC' as Report_Group,
    'Other' as Name,
    6.8 as Percentage_Difference
union all
select '1 Year' as Report_Gap,
    'DEF' as Report_Group,
    'Other' as Name,
    5.7 as Percentage_Difference
union all
select '1 Year' as Report_Gap,
    'DEF' as Report_Group,
    'Operating' as Name,
    1.2 as Percentage_Difference
union all
select '1 Year' as Report_Gap,
    'GHI' as Report_Group,
    'Operating' as Name,
    6.5 as Percentage_Difference
union all
select '1 Year' as Report_Gap,
    'GHI' as Report_Group,
    'Other' as Name,
    -2.0 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'ABC' as Report_Group,
    'Operating' as Name,
    13.6 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'ABC' as Report_Group,
    'Other' as Name,
    5.4 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'DEF' as Report_Group,
    'Other' as Name,
    5.5 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'DEF' as Report_Group,
    'Operating' as Name,
    15.4 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'GHI' as Report_Group,
    'Operating' as Name,
    19.5 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'GHI' as Report_Group,
    'Other' as Name,
    -7.9 as Percentage_Difference
)
select * from MyData
go

Answer 1

You can use PIVOT function as follows:

 WITH PivotData AS (   
 SELECT     Report_Gap,      
 Report_Group, 
 Name,
 Percentage_Difference   
 FROM tablename ) 
 SELECT Report_Gap, Report_Group, Operating, Other 
 FROM PivotData   
 PIVOT(max(Percentage_Difference) 
 FOR Name IN (Operating, Other) ) AS P;

Answer 2

You want conditional aggergation :

SELECT Report_Gap, Report_Group, 
       MAX(CASE WHEN Name = 'Operating' THEN Percentage_Difference END) AS Operating,
       MAX(CASE WHEN Name = 'Other' THEN Percentage_Difference END) AS Other
FROM table t
GROUP BY Report_Gap, Report_Group;

If Report_Group has one or more Percentage_Difference then you might need SUM() instead of MAX().