How do you pivot and unpivot data in SQL?
How do you pivot and unpivot data in SQL?
Pivot turns row values into columns (months across the top); unpivot turns columns back into rows. The portable way to pivot is conditional aggregation.
SELECT product,
SUM(CASE WHEN EXTRACT(MONTH FROM sold_on) = 1 THEN amount ELSE 0 END) AS jan,
SUM(CASE WHEN EXTRACT(MONTH FROM sold_on) = 2 THEN amount ELSE 0 END) AS feb,
SUM(CASE WHEN EXTRACT(MONTH FROM sold_on) = 3 THEN amount ELSE 0 END) AS mar
FROM sales
GROUP BY product;
PostgreSQL has a shorter standard FILTER clause:
SELECT product,
SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sold_on) = 1) AS jan,
SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sold_on) = 2) AS feb
FROM sales GROUP BY product;
-- SQL Server: the IN list holds the values that become column names
SELECT * FROM (SELECT product, month_name, amount FROM sales) s
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.