What are the different ways to pivot columns in SQL?

1 year ago
Emily Johnson
2 minutes

There are several methods in SQL for pivoting columns into rows.

  1. By using the UNPIVOT operator, columns can be transformed into rows. It requires specifying the columns to be converted and the column names for the generated rows. For example:
SELECT customer_id, product, quantity
FROM sales
UNPIVOT
(
  quantity
  FOR product IN (product_A, product_B, product_C)
) AS unpvt;
  1. Using CROSS APPLY: The CROSS APPLY operator can pivot columns into rows. It can be used together with a VALUES clause to convert column values into rows. For example:
SELECT s.customer_id, v.product, v.quantity
FROM sales s
CROSS APPLY 
(
  VALUES 
  ('product_A', s.product_A),
  ('product_B', s.product_B),
  ('product_C', s.product_C)
) AS v (product, quantity);
  1. Using UNION ALL: The UNION ALL operator can combine the result sets of multiple queries into one result set. When using UNION ALL, each query should return the same number of columns and the same data types. For example:
SELECT customer_id, 'product_A' AS product, product_A AS quantity
FROM sales
UNION ALL
SELECT customer_id, 'product_B' AS product, product_B AS quantity
FROM sales
UNION ALL
SELECT customer_id, 'product_C' AS product, product_C AS quantity
FROM sales;

There are common methods for converting columns to rows, the specific method used depends on the database support and the requirements of the query.

bannerAds