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

Answer 1

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