روش تغییر جای سطرها با ستون‌ها در SQL

      CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
    ([color], [Paul], [John], [Tim], [Eric])
VALUES
    ('Red', 1, 5, 1, 3),
    ('Green', 8, 4, 3, 5),
    ('Blue', 2, 2, 9, 1);

    

      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
) src
GROUP BY name;

    

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

    

      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 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 + ')
        ) unpiv
      ) src
      PIVOT
      (
        SUM(value)
        FOR color IN (' + @colsPivot + ')
      ) piv;'

EXEC(@query);