【SQL】行列转换

列转行

1、先将product_id，store1 的值取出来，其中store1 的列名改为 price
其中新增了一列store，第一行值为store1

SELECT product_id, 'store1' store, store1  price FROM products WHERE store1 IS NOT NULL；

2、依次类推，将store2、store3的也查询出来；

SELECT product_id,'store2' store,store2 price From products where store2 is not null;

SELECT product_id,'store3' store,store3 price From products where store3 is not null;

3、最后将三次查询结果取并集，采用union all（union all和union区别，后者对数据取并集并且去重和排序，union all直接取并集）

select product_id,'store1' store,store1 price From products where store1 is not null
union all
SELECT product_id,'store2' store,store2 price From products where store2 is not null
union all
SELECT product_id,'store3' store,store3 price From products where store3 is not null;

最后转换结果显示为：

行转列

先通过查询创建表

CREATE TABLE product_changed (
  product_id INT,
  store VARCHAR(10),
  price DECIMAL(10, 2)
)
SELECT product_id, 'store1' AS store, store1 AS price FROM products WHERE store1 IS NOT NULL
UNION ALL
SELECT product_id, 'store2' AS store, store2 AS price FROM products WHERE store2 IS NOT NULL
UNION ALL
SELECT product_id, 'store3' AS store, store3 AS price FROM products WHERE store3 IS NOT NULL;

1、通过group by 将product_id 进行分组结合聚合函数+IF 将数据写入列中

select product_id,
SUM(if(store = 'store1',price,null)) AS 'store1',
SUM(if(store = 'store2',price,null)) AS 'store2',
SUM(if(store = 'store3',price,null)) AS 'store3'
from product_changed
group by product_id;

2、或者通过case when的语法

select product_id,
SUM(case when store = 'store1' THEN price else null end) AS 'store1',
SUM(case when store = 'store2' THEN price else null end) AS 'store2',
SUM(case when store = 'store3' THEN price else null end) AS 'store3'
from product_changed
group by product_id;