Transposing Data in SQL

Transposing data in SQL refers to the process of swapping rows with columns or vice versa. This can be a useful technique when working with pivot tables, data analysis, and reporting. In this tutorial, we will explore different methods for transposing data in SQL.

Introduction to Transposing Data

To understand the concept of transposing data, let’s consider an example. Suppose we have a table with colors as rows and names as columns, and we want to switch them so that names become rows and colors become columns.

Original Table:

| Color | Paul | John | Tim | Eric |
| — | — | — | — | — |
| Red | 1 | 5 | 1 | 3 |
| Green| 8 | 4 | 3 | 5 |
| Blue | 2 | 2 | 9 | 1 |

Transposed Table:

| Name | Red | Green | Blue |
| — | — | — | — |
| Paul | 1 | 8 | 2 |
| John | 5 | 4 | 2 |
| Tim | 1 | 3 | 9 |
| Eric | 3 | 5 | 1 |

Method 1: Using UNION ALL and Aggregate Functions

One way to transpose data is by using the UNION ALL operator in combination with aggregate functions. Here’s an example query:

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) Red,
  SUM(CASE WHEN color = 'Green' THEN value ELSE 0 END) Green,
  SUM(CASE WHEN color = 'Blue' THEN value ELSE 0 END) Blue
FROM
(
  SELECT color, Paul value, 'Paul' name
  FROM yourTable
  UNION ALL
  SELECT color, John value, 'John' name
  FROM yourTable
  UNION ALL
  SELECT color, Tim value, 'Tim' name
  FROM yourTable
  UNION ALL
  SELECT color, Eric value, 'Eric' name
  FROM yourTable
) src
GROUP BY name;

This query uses UNION ALL to combine the rows from each column into a single table, and then applies an aggregate function (SUM) with a CASE statement to pivot the data.

Method 2: Using UNPIVOT and PIVOT Functions

Another way to transpose data is by using the UNPIVOT and PIVOT functions in SQL Server. Here’s an example query:

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, [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;

This query uses UNPIVOT to transform the columns into rows, and then applies a PIVOT function to pivot the data.

Method 3: Using Dynamic SQL

If you have an unknown number of columns or rows, you can use dynamic SQL to generate the list of items to transpose. Here’s an example query:

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);

This query uses dynamic SQL to generate the list of items to transpose, and then applies a PIVOT function to pivot the data.

Conclusion

In conclusion, transposing data in SQL can be achieved using different methods, including UNION ALL and aggregate functions, UNPIVOT and PIVOT functions, and dynamic SQL. The choice of method depends on the specific requirements of your project and the complexity of your data.