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 ;