How to transpose columns to rows in SQL?

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

ProductID	Attribute1	Attribute2	Attribute3
1	Red	Large	Cotton
2	Blue	Medium	Silk
3	Green	Small	Wool

Try Hands-Om: Fiddle

Create Input Table: Gist

Desired Output

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

ProductID	AttributeName	AttributeValue
1	Attribute1	Red
1	Attribute2	Large
1	Attribute3	Cotton
2	Attribute1	Blue
2	Attribute2	Medium
2	Attribute3	Silk
3	Attribute1	Green
3	Attribute2	Small
3	Attribute3	Wool

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.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery