列转行
新建一个成绩表C
CREATE TABLE C
( XH NUMBER(5),
XM VARCHAR2(100),
KM VARCHAR2(100),
CJ NUMBER(5)
);
插入数据
INSERT INTO C VALUES(1,'张三','语文',87);
INSERT INTO C VALUES(1,'张三','数学',77);
INSERT INTO C VALUES(1,'张三','英语',83);
INSERT INTO C VALUES(2,'张五','语文',85);
INSERT INTO C VALUES(2,'张五','数学',57);
INSERT INTO C VALUES(2,'张五','英语',67);
查询表 SELECT * FROM C;
![](https://img2020.cnblogs.com/blog/1870919/202003/1870919-20200320130429089-441900088.png)
使用PIVOT函数:
SELECT * FROM C
PIVOT (SUM(CJ)FOR KM IN('语文' AS 语文,'数学' as 数学 ,'英语' as 英语));
![](https://img2020.cnblogs.com/blog/1870919/202003/1870919-20200320130540845-1277712192.png)
行转列
新建一个成绩表D
CREATE TABLE D
( XH NUMBER(5),
XM VARCHAR2(100),
语文 NUMBER(5),
数学 NUMBER(5),
英语 NUMBER(5)
);
插入数据
insert into d values(1,'张三',87,77,83);
insert into d values(2,'张五',85,57,67);
查询D表中数据 SELECT * FROM D;
![](https://img2020.cnblogs.com/blog/1870919/202003/1870919-20200320130558577-1001766295.png)
方法一 UNION ALL
SELECT XH,XM,'语文' KM,语文 CJ FROM D
UNION ALL
SELECT XH,XM,'数学' KM,数学 CJ FROM D
UNION ALL
SELECT XH,XM,'英语' KM,英语 CJ FROM D;
![](https://img2020.cnblogs.com/blog/1870919/202003/1870919-20200320130608097-2080108663.png)
方法二 UNPIVOT
select * FROM D
UNPIVOT (CJ FOR KM IN(语文 ,数学 ,英语 ));
![](https://img2020.cnblogs.com/blog/1870919/202003/1870919-20200320130618564-820707966.png)