Oracle行转列详解_oracle 行转列

一、建表与插入数据

1.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;

二、固定行列转换

2.1、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

2.2、Case方式

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

2.3、wmsys.wm_concat行列转换函数

SELECT ID,NAME, wmsys.wm_concat(course || ‘:’||score) course FROM kecheng GROUP BY ID ,NAME;

2.4、使用over(partition by t.u_id)用法

SELECT NAME, wmsys.wm_concat(course ||score) OVER (PARTITION BY NAME) FROM kecheng

三、动态转换

3.1、使用PL/SQL

CREATE OR REPLACE PROCEDURE P_TOYGSEND IS-
–存放最终的SQL-
LV_SQL VARCHAR2(3000);-
–存放连接的SQL-
SQL_COMMOND VARCHAR2(3000);-
–定义游标-
CURSOR CUR IS-
SELECT COURSE FROM KECHENG GROUP BY COURSE;-
BEGIN-
–定义查询开头-
SQL_COMMOND := 'SELECT NAME ';

FOR I IN CUR-
LOOP-
–将结果相连接-
SQL_COMMOND := SQL_COMMOND || ’ ,SUM(DECODE(course,‘’’ || I.COURSE ||-
‘’',score,0)) ’ || I.COURSE;-
END LOOP;

SQL_COMMOND := SQL_COMMOND || ’ from KECHENG group by name’;

LV_SQL := 'INSERT INTO temp_ss ’ || SQL_COMMOND;

EXECUTE IMMEDIATE LV_SQL;-
commit;-
EXCEPTION-
WHEN OTHERS THEN-
ROLLBACK;-
NULL;-

END P_TOYGSEND;

转自：https://blog.csdn.net/w892824196/article/details/82222193