SqlServer行转列，列转行

文章来源：https://leetcode.cn/problems/rearrange-products-table/solution/by-esqiimulme-pjiy/

列转行用union或union all将多列的字段整合到一行。（当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，mysql 会把所有的记录返回，且效率高于union 。）

行转列用case...when或if分类讨论, group by进行分组。

 

 

 

列转行

SELECT product_id, 'store1' store, store1 price FROM products WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' store, store2 price FROM products WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' store, store3 price FROM products WHERE store3 IS NOT NULL;

行转列

SELECT
product_id,
SUM(IF(store = 'store1', price, NULL)) 'store1',
SUM(IF(store = 'store2', price, NULL)) 'store2',
SUM(IF(store = 'store3', price, NULL)) 'store3'
FROM
Products1
GROUP BY product_id ;