行转列、列转行

末蓝、 2021-12-20 13:53 380阅读 0赞

一：行转列  
  
  
模式：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

转载于:https://www.cnblogs.com/andy6/p/6798650.html