Can you unpivot multiple columns in SQL?

Yes, you can unpivot multiple columns in SQL. Unpivoting transforms columns into rows, which is useful for normalizing data or preparing it for analysis. The method to achieve this varies slightly depending on the specific SQL database system you are using, but the general concept remains the same.

Here are a few common approaches:

1. Using the `UNPIVOT` Operator (SQL Server, Oracle):

- SQL Server and Oracle provide a dedicated `UNPIVOT` operator that simplifies the process. This operator takes a list of columns and transforms them into rows, creating new columns for the column name and the corresponding value.

- Example:

SELECT ProductID, Attribute, Value
FROM
(SELECT ProductID, Color, Size, Weight FROM Products)
UNPIVOT
(Value FOR Attribute IN (Color, Size, Weight)) AS unpvt;

- In this example, the `Products` table's `Color`, `Size`, and `Weight` columns are unpivoted into rows, with `Attribute` indicating the original column name and `Value` holding the corresponding value.

2. Using `UNION ALL` (Most SQL Databases):

- If your database does not support the `UNPIVOT` operator, you can achieve the same result using `UNION ALL`. This involves creating a separate `SELECT` statement for each column you want to unpivot and then combining them using `UNION ALL`.

- Example:

SELECT ProductID, 'Color' AS Attribute, Color AS Value FROM Products
UNION ALL
SELECT ProductID, 'Size' AS Attribute, Size AS Value FROM Products
UNION ALL
SELECT ProductID, 'Weight' AS Attribute, Weight AS Value FROM Products;

- This approach is more verbose but works across most SQL databases.

3. Using `CROSS APPLY` (SQL Server):

- In SQL Server, you can also use `CROSS APPLY` with a `VALUES` clause to unpivot columns.

- Example:

SELECT ProductID, Attribute, Value
FROM Products
CROSS APPLY (VALUES
('Color', Color),
('Size', Size),
('Weight', Weight)
) AS unpvt (Attribute, Value);

- This method is concise and can be more readable for some users.

In summary, unpivoting multiple columns in SQL is achievable through various methods, including the `UNPIVOT` operator, `UNION ALL`, and `CROSS APPLY`. The best approach depends on your specific database system and personal preference. Each method effectively transforms column data into rows, making it easier to analyze and manipulate.

Learn more about this