行转列、列转行
一：行转列
模式：N行转一列 本实验N取3
1.建表
SQL> create table grade(id int,name varchar2(10),subject varchar2(10),grade number(3));
2.插入实验数据
insert into grade values(1,'Andy','Yuwen',11);
insert into grade values(2,'Andy','Math',22);
insert into grade values(3,'Andy','English',33);
insert into grade values(4,'Zhang','English',44);
insert into grade values(5,'Zhang','Math',55);
insert into grade values(6,'Zhang','Yuwen',66);
insert into grade values(7,'Tao','Yuwen',77);
insert into grade values(8,'Tao','Math',88);
commit;
3.查询
SQL> select * from grade;
ID NAME SUBJECT GRADE
---------- ---------- ---------- ----------
1 Andy Yuwen 11
2 Andy Math 22
3 Andy English 33
4 Zhang English 44
5 Zhang Math 55
6 Zhang Yuwen 66
7 Tao Yuwen 77
8 Tao Math 88
8 rows selected.
4.错误示范，缺失 sum 。
SQL>
select name,
decode(subject,'Yuwen', grade,null) "Language",
decode(subject,'Math', grade,null) "Math",
decode(subject,'English', grade,null) "English"
from grade;
NAME Language Math English
---------- ---------- ---------- ----------
Andy 11
Andy 22
Andy 33
Zhang 44
Zhang 55
Zhang 66
Tao 77
Tao 88
4. 转换。正确示范
方法一：
SQL>
select name,
sum(decode(subject,'Yuwen', grade,0)) "Yuwen",
sum(decode(subject,'Math', grade,0)) "Math",
sum(decode(subject,'English', grade,0)) "English"
from grade
group by name;
NAME Yuwen Math English
---------- ---------- ---------- ----------
Zhang 66 55 44
Andy 11 22 33
Tao 77 88 0
方法二：
SELECT name,
SUM(CASE WHEN subject='Yuwen' THEN grade ELSE 0 END) AS "Yuwen",
SUM(CASE WHEN subject='Math' THEN grade ELSE 0 END) AS "Math",
SUM(CASE WHEN subject='English' THEN grade ELSE 0 END) AS "English"
FROM grade
group by name;
二、列转行
模式：一列转N行，本实验N取3 （说明：相同类型数据确定N的取值，比如：YUWEN，MATH，ENGLISH 所以取3）
1.建表
create table score(id int,name varchar2(10),Yuwen number(3),Math number(3),English number(3));
2.插入数据
insert into score values(1,'Zhang',11,22,33);
insert into score values(2,'Andy',44,55,66);
insert into score(id,name,Yuwen) values(3,'Tao',77);
commit;
3.查询
SQL> select * from score;
ID NAME YUWEN MATH ENGLISH
---------- ---------- ---------- ---------- ----------
1 Zhang 11 22 33
2 Andy 44 55 66
3 Tao 77
4.转换
SELECT name, 'Yuwen' subject,Yuwen grade FROM score
UNION ALL
SELECT name, 'Math' subject,Math grade FROM score
UNION ALL
SELECT name, 'English' subject,English grade FROM score
order by name;
NAME SUBJECT GRADE
---------- ------- ----------
Andy Math 55
Andy English 66
Andy Yuwen 44
Tao English
Tao Math
Tao Yuwen 77
Zhang Math 22
Zhang English 33
Zhang Yuwen 11
9 rows selected.
注意：加引号表示是字符串，不加引号取列的值
模式：N列转成一行
1.建表
create table test(dname varchar2(10),ename varchar2(10));
2.插入数据
insert into test values('Guowang','Andy');
insert into test values('Guowang','Zhang');
insert into test values('Guowang','Chong');
insert into test values('Nvwang','Tao');
insert into test values('Nvwang','Ye');
insert into test values('happy','Leaf');
commit;
3.查询
SQL> select * from test;
DNAME ENAME
---------- ----------
Guowang Andy
Guowang Zhang
Guowang Chong
Nvwang Tao
Nvwang Ye
happy Leaf
6 rows selected.
目标转换模式：
DNAME NAME
---------- ----------------
Guowang Andy,Zhang,Chong
Nvwang Tao,Ye
happy Leaf
4. 转换
方法一：
-- 一种简单通过函数的方法，有oracle版本限制，作为一种备选方案。
SQL> col name for a40
SQL> select dname,WMSYS.WM_CONCAT(ename) NAME from test group by dname;
DNAME NAME
---------- ----------------------------------------
Guowang Andy,Chong,Zhang
Nvwang Tao,Ye
happy Leaf
方法二：
构造出来一个层次，利用层次查询处理
4.2.1 -- 虚构员工编号
SQL> SELECT dname, ename, ROW_NUMBER() OVER (ORDER BY dname) rn FROM test;
DNAME ENAME RN
---------- ---------- ----------
Guowang Andy 1
Guowang Zhang 2
Guowang Chong 3
Nvwang Tao 4
Nvwang Ye 5
happy Leaf 6
4.2.2-- 领导编号
SELECT dname, ename, rn, LEAD (rn) OVER (PARTITION BY dname ORDER BY rn) rn1
FROM (SELECT dname, ename, ROW_NUMBER () OVER (ORDER BY dname) rn FROM test);
DNAME ENAME RN RN1
---------- ---------- ---------- ----------
Guowang Andy 1 2
Guowang Zhang 2 3
Guowang Chong 3
Nvwang Tao 4 5
Nvwang Ye 5
happy Leaf 6
4.2.3 转换
col name for a40;
SELECT dname, SUBSTR(MAX (SYS_CONNECT_BY_PATH (ename, ',')), 2) NAME
FROM (
SELECT dname, ename, rn, LEAD (rn) OVER (PARTITION BY dname ORDER BY rn) rn1
FROM (SELECT dname, ename, ROW_NUMBER () OVER (ORDER BY ename) rn
FROM test)
)
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY dname;
DNAME NAME
---------- -----------------------------
happy Leaf
Nvwang Ye,Tao
Guowang Zhang,Chong,Andy
