sql行列互相装换的sql语句

sql行列互相装换的sql语句

1.列转行

准备测试数据

 create table kecheng
(
  id     NUMBER,
  name   VARCHAR2(20),
  course VARCHAR2(20),
  score  NUMBER
);
-- 插入测试数据
insert into kecheng (id, name, course, score)
values (1, '张三', '语文', 67);
insert into kecheng (id, name, course, score)
values (1, '张三', '数学', 76);
insert into kecheng (id, name, course, score)
values (1, '张三', '英语', 43);
insert into kecheng (id, name, course, score)
values (1, '张三', '历史', 56);
insert into kecheng (id, name, course, score)
values (1, '张三', '化学', 11);
insert into kecheng (id, name, course, score)
values (2, '李四', '语文', 54);
insert into kecheng (id, name, course, score)
values (2, '李四', '数学', 81);
insert into kecheng (id, name, course, score)
values (2, '李四', '英语', 64);
insert into kecheng (id, name, course, score)
values (2, '李四', '历史', 93);
insert into kecheng (id, name, course, score)
values (2, '李四', '化学', 27);
insert into kecheng (id, name, course, score)
values (3, '王五', '语文', 24);
insert into kecheng (id, name, course, score)
values (3, '王五', '数学', 25);
insert into kecheng (id, name, course, score)
values (3, '王五', '英语', 8);
insert into kecheng (id, name, course, score)
values (3, '王五', '历史', 45);
insert into kecheng (id, name, course, score)
values (3, '王五', '化学', 1);
commit;

1.1使用decode函数的方式(mysql不支持decode函数)

SELECT ID,NAME,
SUM(DECODE(course,'语文',score,0)) 语文,--这里使用max,min都可以
SUM(DECODE(course,'数学',score,0)) 数学,
SUM(DECODE(course,'英语',score,0)) 英语,
SUM(DECODE(course,'历史',score,0)) 历史,
SUM(DECODE(course,'化学',score,0)) 化学
FROM kecheng
GROUP BY ID ,NAME

1.2使用Case when 函数的方式,该方法支持mysql数据库

SELECT ID,NAME,
MAX(CASE WHEN course='语文' THEN score ELSE 0 END) 语文,
MAX(CASE WHEN course='数学' THEN score ELSE 0 END) 数学,
MAX(CASE WHEN course='英语' THEN score ELSE 0 END) 英语,
MAX(CASE WHEN course='历史' THEN score ELSE 0 END) 历史,
MAX(CASE WHEN course='化学' THEN score ELSE 0 END) 化学
FROM kecheng
GROUP BY ID ,NAME

下面方法了解回顾即可

-- (3) wmsys.wm_concat行列转换函数
SELECT ID,NAME,
wmsys.wm_concat(course || ':'||score) course
FROM kecheng
GROUP BY ID ,NAME;

-- (4) 使用over(partition by t.u_id)用法
SELECT NAME, wmsys.wm_concat(course ||score) OVER (PARTITION BY NAME) FROM kecheng

-- (5) 使用povot函数
select * from ( select id , name , score , course from kecheng) pivot (sum(score) for course in ('数学','化学','英语','语文','历史') )

2.列转行

准备测试数据

create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);
 -- 插入测试数据
 insert into Fruit values(1,'苹果',1000,2000,3300,5000);
insert into Fruit values(2,'橘子',3000,3000,3200,1500);
insert into Fruit values(3,'香蕉',2500,3500,2200,2500);
insert into Fruit values(4,'葡萄',1500,2500,1200,3500);
commit;

2.1 使用unpivot函数(应该只用oracle函数支持)

select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )

2.2使用union的方式(也同样支持mysql)

select id, name ,'Q1' jidu, (select q1 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,'Q2' jidu, (select q2 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,'Q3' jidu, (select q3 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,'Q4' jidu, (select q4 from fruit where id=f.id) xiaoshou from Fruit f