How to transpose columns to rows in SQL?

Let's see how to transpose columns to rows in SQL. That is, how to reshape the table so that certain columns of a table are moved to rows.

By Prabhakaran S  ·  September 2023

Problem

How to transpose columns to rows in SQL?

This essentially requires reshaping the table in such a way that certain columns of a table are moved to rows.

Input

ProductIDAttribute1Attribute2Attribute3
1RedLargeCotton
2BlueMediumSilk
3GreenSmallWool

Try Hands-Om: Fiddle

Create Input Table: Gist

Desired Output

We want only those records containing the maximum value of Price for each category.

ProductIDAttributeNameAttributeValue
1Attribute1Red
1Attribute2Large
1Attribute3Cotton
2Attribute1Blue
2Attribute2Medium
2Attribute3Silk
3Attribute1Green
3Attribute2Small
3Attribute3Wool

There are multiple ways to do this. Let’s look at some of them.

Solution 1:

Using UNION ALL

SELECT ProductID, 'Attribute1' AS AttributeName, Attribute1 AS AttributeValue
FROM Products
UNION ALL
SELECT ProductID, 'Attribute2', Attribute2
FROM Products
UNION ALL
SELECT ProductID, 'Attribute3', Attribute3
FROM Products
ORDER BY ProductID, AttributeName;

Explanation:

This solution will transpose the Attribute1, Attribute2, and Attribute3 columns from the Products table into rows under the AttributeName and AttributeValue columns, maintaining the association with the respective ProductID.

Solution 2:

Using CROSS JOIN and CASE Statement

SELECT
    p.ProductID,
    attrs.AttributeName,
    CASE attrs.AttributeName
        WHEN 'Attribute1' THEN p.Attribute1
        WHEN 'Attribute2' THEN p.Attribute2
        WHEN 'Attribute3' THEN p.Attribute3
    END AS AttributeValue
FROM
    Products p
CROSS JOIN
(
    SELECT 'Attribute1' AS AttributeName
    UNION ALL
    SELECT 'Attribute2'
    UNION ALL
    SELECT 'Attribute3'
) AS attrs
ORDER BY
    p.ProductID,
    attrs.AttributeName;

Steps Involved:

  1. Create a derived table (or subquery) with attribute names.
  2. Use the CROSS JOIN to join this derived table with the main table.
  3. Use the CASE statement to pick values based on the attribute names.

Explanation:

This method avoids multiple full table scans (which can happen in the UNION ALL approach), and might be more efficient on larger datasets, especially if there are many attributes to transpose.

The advantage of this solution is scalability. If you add more attributes in the future, you just need to add more rows to the derived attrs table. You don’t need to add an entire new UNION ALL section.

  1. SQL for Data Science – Level 1
  2. SQL for Data Science – Level 2
  3. SQL for Data Science – Level 3
  1. Introduction to SQL
  2. SQL Window Functons – Made Simple and Easy
  3. SQL Subquery