Easy way to convert SQL ranks and columns

While SQL's PIVOT function seems to be suitable for row-and-column conversion, its complexity can be prohibitive. If you want to achieve this in a easier way, consider the following alternative:

Use UNION ALL, aggregate functions, and CASE statements

This method uses UNION ALL to expand the data and then uses the aggregate function and CASE statement for perspective:

SELECT name,
  SUM(CASE WHEN color = 'Red' THEN value ELSE 0 END) AS Red,
  SUM(CASE WHEN color = 'Green' THEN value ELSE 0 END) AS Green,
  SUM(CASE WHEN color = 'Blue' THEN value ELSE 0 END) AS Blue
FROM
(
  SELECT color, Paul AS value, 'Paul' AS name
  FROM yourTable
  UNION ALL
  SELECT color, John AS value, 'John' AS name
  FROM yourTable
  UNION ALL
  SELECT color, Tim AS value, 'Tim' AS name
  FROM yourTable
  UNION ALL
  SELECT color, Eric AS value, 'Eric' AS name
  FROM yourTable
) AS src
GROUP BY name

Static Deconstruction and Perspective

If you know the value you want to convert, use hardcoded values ​​for deconstruction and perspective:

SELECT name, [Red], [Green], [Blue]
FROM
(
  SELECT color, name, value
  FROM yourTable
  UNPIVOT
  (
    value FOR name IN (Paul, John, Tim, Eric)
  ) AS unpiv
) AS src
PIVOT
(
  SUM(value)
  FOR color IN ([Red], [Green], [Blue])
) AS piv

Dynamic Perspective

For unknown number of columns and colors, use dynamic SQL to generate a deconstructed and perspective list:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX),
    @colsPivot AS NVARCHAR(MAX)

SELECT @colsUnpivot = STUFF((SELECT ','   QUOTENAME(C.name)
         FROM sys.columns AS C
         WHERE C.object_id = OBJECT_ID('yourtable') AND
               C.name  'color'
         FOR XML PATH('')), 1, 1, '')

SELECT @colsPivot = STUFF((SELECT ','
                         QUOTENAME(color)
                     FROM yourtable AS t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        , 1, 1, '')

SET @query = 'SELECT name, '   @colsPivot   '
  FROM (
    SELECT color, name, value
    FROM yourtable
    UNPIVOT
    (
      value FOR name IN ('   @colsUnpivot   ')
    ) AS unpiv
  ) AS src
  PIVOT
  (
    SUM(value)
    FOR color IN ('   @colsPivot   ')
  ) AS piv'

EXEC(@query)

All three methods produce the following results: