How to pivot table and get data in below format

Question

Currently I have data in below As-Is format

And have requirement to get in below format

I am using SQL 2008 R2 version

I have spend some time to get this working using Pivot \ Unpivot, but no luck. It would be great if someone can help me to resolve this mystry

score 0 · Answer 1 · answered Dec 16 '15 at 08:16

try

 ;with a as (select * , row_number() over (order by (select 1)) r from @t),

 b as (select * , row_number() over (partition by agg,RManager order by r) r1 from a)

select t.RManager,t.Score,t1.Score,t.Agg,t.Rank,t1.Rank
from b t join (select * from b where r1=2  ) t1 on  t.Agg=t1.Agg and t.RManager=t1.RManager
where t.r1=1 order by agg desc

Data

declare @t table (RManager char(1),MeasureName varchar(10),Score float,Agg varchar(5),  [Rank] int)
insert into @t (RManager,MeasureName,Score,Agg,[Rank])
values('A', 'ATVScore', 0.03,   'WTD',  1),
('B',   'ATVScore', 0.05,   'WTD',  2),
('C',   'ATVScore', 0.12,   'WTD',  3),
('A',   'ATVScore', 34, 'MTD',  2),
('B','ABCScore',    12  ,'MTD', 3),
('C',   'ABCScore', 112,    'MTD',  1),
('A',   'ABCScore', 23, 'WTD',  3),
('B',   'ABCScore', 34, 'WTD',  2),
('C',   'ABCScore', 45, 'WTD',  1),
('A',   'ABCScore', 123 ,'MTD', 1),
('B',   'ABCScore', 34  ,'MTD', 3),
('C',   'ABCScore', 45  ,'MTD', 2)

Felix Pamittan · Accepted Answer · 2015-12-16T03:13:17.733

You can do a conditional aggregation:

SELECT
    RegionalManager,
    ATVScore    = MAX(CASE WHEN MeasureName = 'ATVScore' THEN Score END),
    ABCScore    = MAX(CASE WHEN MeasureName = 'ABCScore' THEN Score END),
    Agg,
    ATVRank     = MAX(CASE WHEN MeasureName = 'ATVScore' THEN [Rank] END),
    ABCRank     = MAX(CASE WHEN MeasureName = 'ABCScore' THEN [Rank] END)
FROM tbl 
GROUP BY
    RegionalManager, Agg

If you have unknown values for MeasureName, you need to do it dynamically:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql +
'SELECT
    RegionalManager' + CHAR(10)

SELECT @sql = @sql +
'   , MAX(CASE WHEN MeasureName = ''' + MeasureName  + ''' THEN Score END) AS' + QUOTENAME(MeasureName) + CHAR(10)
FROM(
    SELECT DISTINCT MeasureName FROM tbl
) t

SELECT @sql = @sql +
'   , Agg' + CHAR(10)

SELECT @sql = @sql +
'   , MAX(CASE WHEN MeasureName = ''' + MeasureName  + ''' THEN [Rank] END) AS' + QUOTENAME(MeasureName) + CHAR(10)
FROM(
    SELECT DISTINCT MeasureName FROM tbl
) t

SELECT @sql = @sql +
'FROM tbl
GROUP BY
    RegionalManager, Agg'

EXECUTE sp_executesql @sql

How to pivot table and get data in below format

2 Answers2